Question

How can you capture the length of a column when it is a Text, NText and/or Image data type?

Answer 1

• Use the DATALENGTH command to capture the length.
• The LEN command is invalid for Text, NText and Image data types.

If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH(). Even LEN() documentation has an information that to get number of bytes to represent the extension you should use DATALENGTH().