Difference between LEN and DataLength in SQL Server 2008

Recently I have seen that people face confusion between two very useful string functions of Microsoft SQL Server LEN and DATALENGTH. There is great and technical difference between these two functions. Basically LEN returns integer number by counting each character in given string after removing right side blanks whereas DATALENGTH will return number of byte required to store that expression.

Let me make your concept more clearly by giving you short example.

DECLARE @STR varchar(10)

SET @STR='RITESH '

SELECT @STR AS 'original value',LEN(@str) AS 'Length', DATALENGTH(@STR) AS 'DataLength'

Original value will be my name with one blank space after name (right end), LEN will give you 6, it will just count character of my name and will remove right side blank whereas DATALENGTH  will give you 7 as it will return bytes which will be needed to store string variable @STR.

Hope it helps.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com