Data length in ntext column?

Use DataLength()

SELECT * FROM YourTable WHERE DataLength(NTextFieldName) > 0

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.

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

Data length in ntext column?

Related

Recent Posts