Data length in ntext column?

Question

How to find out the length and size of data in an ntext column in SQL? - This is longer than 8000 bytes, so I can not attribute it to varchar. Thank.

Answer 1

Use DataLength ()

SELECT * FROM YourTable WHERE DataLength(NTextFieldName) > 0 

Answer 2

Key in the question: use DATALENGTH() . Note that this has a different behavior for 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() does not remove trailing spaces and returns the number of bytes, while LEN() trims trailing spaces and returns the number of characters.

Answer 3

 Select Max(DataLength([NTextFieldName])) from YourTable