June 14, 2014

SQL Server - Len() vs DataLength()

DataLength returns the length of any expression in bytes which also includes trailing spaces if exits. DataLength is specially useful with varchar, varbinary, text, image, nvarchar and ntext data types because these data types can store variable-lenght data.

Len returns the number of character, rather than the number of bytes, of the given expression excluding trailing spaces.

So, we can say Len function will first right trim the expression and counts the expression where as DataLength function gives the storage space required for the characters.

For Example:

select Len('Avesh') -- Returns: 5
select Len('Avesh ') -- Returns: 5
select Len(' Avesh') -- Returns: 6
select Len(' Avesh ') -- Returns: 6

select DataLength('Avesh') -- Returns: 5
select DataLength('Avesh ') -- Returns: 6
select DataLength(' Avesh') -- Returns: 6
select DataLength(' Avesh ') -- Returns: 7

No comments: