Post
Ask Question

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

    len

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

    len function 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

    datalength

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

    datalength function 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

    SQL query 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.