Monday, August 28, 2006

Returning the Length of an Text or nText Field

In SQL Server, if you try len(FieldName) to return the number of characters in a field and the filed is a type of Text or nText, you get an error:

Argument data type text is invalid for argument 1 of len function

The answer to this is the Datalength() function which will return the length of any expression. This can be used on all data types including text, ntext, image and varbinary.

It returns the actual number of bytes in the field.


Anonymous said...

The following results should be noted:

select datalength('a'), datalength(N'a')

returns 1, 2

So if you have an nText field and want to know how many characters are in it, you will have to do "select datalength(field)/2 from ..."

Anonymous said...

Thanks a lot!