Difference between Len() and DataLength() functions

Len() function gives the length of the characters in a string by excluding the trailing spaces, but datalength() function will give the length of characters in a string by including trailing spaces also because it returns the amount of storage required to represent a column.

Datalength() function works on nvarchar and nchar accurately by considering 2 bytes per character where len() function does not.

Datalength() function works on text and ntext where len() function does not works.

Check This code

create table test(val1 char(10), val2 nchar(10))


insert into test values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test

Output is
3 3   10   20

create table test_v(val1 varchar(10), val2 nvarchar(10))


insert into test_v values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test_v

Output is
3   3   3   6


create table test_c(val1 text, val2 ntext)


insert into test_c values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from  test_c

Above query gives the below error, because len() function not support text data types
Argument data type text is invalid for argument 1 of len function.

select datalength(val1),datalength(val2) from  test_c

Output is
3 6


Reference : MSDN_Len
                   MSDN_DataLength