The differences between LEN and DATALENGTH in SQL Server!

I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion.

First let’s take a look what Books On Line has to say about these two functions

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

Let’s take a look at some more data, first create this table

create table #TeslLen (	CharCol char(5), 
			VarCharCol varchar(5),
			NCharCol nchar(5), 
			NVarCharCol nvarchar(5))


insert #TeslLen values('A','A','A','A')
insert #TeslLen values('AB','AB','AB','AB')
insert #TeslLen values('ABC','ABC','ABC','ABC')
insert #TeslLen values('ABCD','ABCD','ABCD','ABCD')
insert #TeslLen values('ABCDE','ABCDE','ABCDE','ABCDE')
insert #TeslLen values(' ',' ',' ',' ')

Now run the following query

select CharCol as Value,len(CharCol) as LenChar,DATALENGTH(CharCol) as DLenChar,
	len(VarCharCol) as LenVarChar,DATALENGTH(VarCharCol)as DLenVarChar,
	len(NCharCol) as LenNChar,DATALENGTH(NCharCol) as DLenNChar,
	len(NVarCharCol) as LenNVarChar,DATALENGTH(NVarCharCol) as DLenNVarChar
from #TeslLen

Here is the output for all the columns with LEN and DATALENGTH

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum