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

Leave a Reply


WordPress.com Logo


Google photo


Twitter picture


Facebook photo


Connecting to %s

%d bloggers like this: