The differences between LEN and DATALENGTH in SQL Server

 

The differences between LEN and DATALENGTH in SQL Server!


LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH
Returns the number of bytes used to represent any expression.

So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the charaters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.

Take a look at this example

 

 

declare @v nchar(5)

select @v ='ABC  '

 

 

select len(@v),datalength(@v)

The output for len is 3 while the output for datalength =10. The reason that datalength returns the value 10 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per charaters