I want to count the characters in an ntext field. Following Pinal Dave's advice, I am using datalength. But this function seems to double the value I am looking for. When I copy the value in the field into word and count the characters, I get 1502. But when I do
select datalength(result) from myTable
I get a value of 3004 characters.
Why?
Unicode is two bytes per character. Your
NText
field is a Unicode string.DataLength()
returns the number of bytes required to store a field,Len()
returns the number of characters.From
Len()
: "Returns the number of characters of the specified string expression, excluding trailing blanks."DataLength
does not exclude trailing blanks. For Unicode strings you can useDataLength( UnicodeStringExpression ) / DataLength( N'#' )
to get the length in characters.In general
DataLength( Left( Coalesce( StringExpression, '#' ), 1 ) )
will return the number of bytes per character sinceCoalesce
returns a value based on data type precedence where Unicode strings are higher precedence than byte-string types (char
andvarchar
).