Data length in ntext column?

Nick_Crowther_14072 0
Viewed 56776

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.

sql-server
3 Answers

Use DataLength()

SELECT * FROM YourTable WHERE DataLength(NTextFieldName) > 0
user_226500

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.

onedaywhen_153540
Related