Calculating length of ntext data type with T-SQL

We use the LEN() function when calculating the length of a String field. But when you use this function in fields with ntext datatype, you will get the following error.

Argument data type ntext is invalid for argument 1 of len function.

In this case, another function you can use as an alternative is DATALENGTH()

But DATALENGTH returns us the number of bytes instead of the number of characters. Each character in the String is 2 bytes.

For example, if the result is 28, it means it's 14 characters.

select top 1 datalength(NameSurname), NameSurname from Customer order by CreateDate