Difference Between LEN and DATALENGTH Function in SQL

This blog describe the difference between LEN and DATALENGTH function in SQL database.

LEN Function

The LEN function returns the number of characters in a variable. It also removes the trailing spaces and then return the length.

Example 1


DECLARE @Name VARCHAR(20)='rakesh'
SELECT LEN(@Name) as [len]

Output



Example 2


DECLARE @Name VARCHAR(20)='rakesh '
SELECT LEN(@Name) as [len]

Output



When we observe above variable assigned 'rakesh ' string after that added 3 spaces. The LEN function removes trailing spaces not leading spaces. 

DATALENGTH Function

DATALENG function returns the number of bytes occupy in a variable. It also considered the spaces also.

Example 1


DECLARE @Name VARCHAR(20)='rakesh'
SELECT DATALENGTH(@Name) as [DataLength]

Output



Example 2


DECLARE @Name VARCHAR(20)=' rakesh '
SELECT DATALENGTH(@Name) as [DataLength]

Output



In above example before 'r' and after 'h' we added space that why data length should be 8.

Example 3


DECLARE @Name NVARCHAR(20)=' rakesh '
SELECT DATALENGTH(@Name) as [DataLength]

Output

NOTE

In varchar each character is allows 1 byte. It does not support Unicode characters.

In nvarchar each character is allows2 byte. It support's Unicode characters.