>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.
Advertisements

Posted on April 20, 2011, in Sql Server 2005, Sql Server 2008, SQL Server Basics, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: