Data length in ntext column?

Question

How do you find out the length/size of the data in an ntext column in SQL? - It's longer than 8000 bytes so I can't cast it to a varchar. Thanks.

Answer 1

Use DataLength()

Answer 2

The clue's in the question: use DATALENGTH(). Note it has a different behaviour to LEN():

SELECT LEN(CAST('Hello   ' AS NVARCHAR(MAX))), 
       DATALENGTH(CAST('Hello   ' AS NVARCHAR(MAX))), 
       DATALENGTH(CAST('Hello   ' AS NTEXT))

returns 5, 16, 16.

In other words, DATALENGTH() doesn't remove trailing spaces and returns the number of bytes, whereas LEN() trims the trailing spaces and returns the number of characters.