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)
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
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: