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 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 SELECT DATALENGTH('ABC  ') will return 5 as output.

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