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


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



Example 2

1. DECLARE @Name VARCHAR(20)='rakesh '
2. 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


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



Example 2

1. DECLARE @Name VARCHAR(20)=' rakesh '
2. 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

1. DECLARE @Name NVARCHAR(20)=' rakesh '
2. 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.