>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 ‘ (SELECT LEN(‘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
SELECT MAX(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, DATALENGTH() function is more suitable. As according to BOL “Returns the number of bytes used to represent any expression”. That is why our previous query with DATALENGTH() function SELECT DATALENGTH(‘ABC ‘) will return 5 as output.