I was asked this question, “What is the differences between LEN and DATALENGTH in sql server”, a couple of times in last fortnight so I decided to write a post regarding LEN vs DATALENGTH.
LEN: For a string, it will return the number of length of the character used, instead of number of bytes, excludes trailing spaces.
DATALENGTH: For a string, it will return the number of bytes used to represent any expression.
This implies that LEN function will first right trim the string and then returns a count of the characters; whereas the DATALENGTH function does not right trim the string and returns the storage space required for the characters.
The other main difference is that LEN don’t work for Text and nText columns, but DataLength does work.
Let’s have a look at an example:
DECLARE @string nCHAR(8)
SELECT @string =’Count ‘
SELECT LEN(@string) AS [LEN],datalength(@string) AS [DATALENGTH]
The output for LEN is 5 while the output for DataLenght =16. The reason that datalength returns the value 16 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per characters