CODEWITHSUNDEEP
sql-server
Nick
Nick

Reputation: 5696

Data length in ntext column?

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.

Upvotes: 51

Views: 59491

Answers (3)

Steve D
Steve D

Reputation: 11

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

Upvotes: 1

korro
korro

Reputation: 1577

Use DataLength()

SELECT * FROM YourTable WHERE DataLength(NTextFieldName) > 0

Upvotes: 76

onedaywhen
onedaywhen

Reputation: 57093

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.

Upvotes: 20

Related Questions