why does sql server's datalength function double the length of my field?

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?

2012-04-04 19:55
by bernie2436

Unrelated - be careful of year(entrydate). That can't use an index now. While entrydate >= '2012-01-01' and entrydate < '2013-01-01' woul - Brian White 2012-04-04 20:10

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.

2012-04-04 20:02
by HABO

if you want to know the length of a text field, you are stuck with datalength because len() wont work. So does that mean that you have to always have the datalength of the character (because unicode is 2 bytes per character) - bernie2436 2012-04-04 20:15

@akh2103 - You simply need to divide the result of DataLength by two to get the number of characters. You can divide by DataLength( N'X' ) as a hint/reminder - HABO 2012-04-04 21:32

And Len() ignores trailing spaces - StevenWhite 2013-07-23 19:19