Determining the Length of SQL Server Text Data
When it is necessary to determine the length of character data in SQL Server, the Len function can be used. This function is not compatible with Text or NText data, so an alternative approach is required.
A common task in Transact-SQL is obtaining the length of a string that is held in a Char or VarChar column. I described the process, using the Len function, in an earlier article. However, this function is only compatible with some of the data types available in SQL Server. If you need to determine the number of characters present in a Text or National Text (NText) value, you must use a different method.
To demonstrate, run the following script against a new, test database. This creates a simple table with four textual columns, and inserts a single row.
CREATE TABLE TextLengthTest
INSERT INTO TextLengthTest VALUES('Test', 'Test', 'Test', 'Test')
Obtaining the length of the VarChar and VarChar(Max) columns works as you might expect.
SELECT len(TestVarchar) FROM TextLengthTest -- 4
SELECT len(TestVarcharMax) FROM TextLengthTest -- 4
Unfortunately, when you try to use the Len function for the other two columns, you run into problems. Try running either of the following two queries:
SELECT len(TestText) FROM TextLengthTest
SELECT len(TestNText) FROM TextLengthTest
You will see that these SELECT statements cause errors. The text of the error explains that the data type is incompatible with the Len function:
Argument data type ntext is invalid for argument 1 of len function.
There is a simple way to determine the length of the data in the other two columns. The DataLength function returns the number of bytes that a stored value requires. In the case of a Text column, each character in the stored text is represented by a single byte, so the return value matches the length of the text.
When you run DataLength against an NText column you must remember that each character in the text is stored using two bytes of data. A simple division of the result by two gives the length of the stored string.
To demonstrate, execute the following two queries. You will see that the correct lengths are returned.
SELECT datalength(TestText) FROM TextLengthTest -- 4
SELECT datalength(TestNText) / 2 FROM TextLengthTest -- 4
NB: DataLength works with any data type. This makes it useful with information held in Binary or Image columns, as it allows you to find out how much data is present in the column.
11 February 2014