Wednesday, April 20, 2011

SQL Server: LEN() or DATALENGTH()

According to few readers of my Previous post “Misconceptions about CHAR datatype” Len() is not a perfect function to get data length as it skips trailing spaces. Meaning if we have string like ‘ABC ‘ (SELECTLEN('ABC ')), LEN() function will return 3 instead of 5 as result because it excludes trailing blanks.

I remember, when one my of colleague was importing SSN (Social Security Numbers) from one table to other but with following query he verified that each SSN is in correct 9 digit formats

SELECTMAX(LEN(SSN))FROM SSNTable

On executing above query, he got 9 as output (which was a green signal) but when he tried to import, query failed with error because targeted table had SSN column with only 9 character space allowed and there were rows which had more then 9 character of length.

On further investigation he found that few rows contains spaces at the end of actual data which were ignored by LEN() function.

(NOTE: spaces at start like ' ABC' are not ignored by LEN() function)

For such situations you can use, DATALENGTH() function but it never returns length of a string but number of bytes a data is taking. As according to BOL “Returns the number of bytes used to represent any expression”. That is why our previous query with DATALENGTH() function SELECTDATALENGTH('ABC ')will return 5 as output.

But one keep in mind that if string is of NVARCHAR then SELECTDATALENGTH(CAST('ABC ' AS NVARCHAR(50)) WILL RETURN 8 AS 1 varchar character holds two bytes.

Translate

About Me

Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite penciles.