Difference Between LEN and DATALENGTH Function in SQL

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.