Similarities and differences between LEN and DATALENGTH functions in SQL Server

Introduction

This article explains the similarities and differences between LEN and DATALENGTH functions in SQL Server.
Both LEN and DATALENGTH functions as the name suggest are used to determine lengths, though they have some similarities they are still different from each other and have their own specific usage.
This article is applicable to SQL Server versions 2005, 2008, 2008R2, 2012, 2014 and higher.

 Similarities between LEN and DATALENGTH functions in SQL Server

Following are the similarities between LEN and DATALENGTH functions in SQL Server.

1. Both LEN and DATALENGTH functions return output as INT and BIGINT if the input expression is of type VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) data types.

2. Both LEN and DATALENGTH functions return NULL if the input expression is NULL. Example, the following will return value as NULL.

SELECT LEN(NULL) AS [LEN]
SELECT DATALENGTH(NULL) AS [DATALENGTH]

3. Both LEN and DATALENGTH functions return same output if the input expression is a string without trailing blank spaces. The reason for same output is because the size of one character is one byte and hence whether count of bytes for each character is returned or count the characters is returned, in both cases the value is same.

Example, the output is 13 for the string “Ashwani Singh” that’s because LEN returns the count of characters while DATALENGTH returns the size i.e. (size of one character) x (total characters) (1 x 13 = 13).

SELECT LEN('Ashwani Singh') AS [LEN]
SELECT DATALENGTH('Ashwani Singh') AS [DATALENGTH]

Differences between LEN and DATALENGTH functions in SQL Server

Following are the differences between LEN and DATALENGTH functions in SQL Server.

LEN

1. It is a string function i.e. it considers the input expression as string. Example, an integer value will also be considered as string and the number of characters will be returned. The following will return output 2.

SELECT LEN(12) AS [LEN]

2. Returns number of characters present in the input expression.

3. Trailing blank spaces are not considered while determining the number of characters of the input expression. Example, the following will return output 13.

SELECT LEN('Ashwani singh   ') AS [LEN]

4. It returns value 0 as length for Blank space character. Example, the following will return value 0.

SELECT LEN(' ') AS [LEN]

DATALENGTH

1.It is a data type function i.e. it considers the input expression of the respective datatype. Example, an Integer value will be considered as Integer and its size in bytes will be returned. The following will return output 4 i.e. size of an Integer.

SELECT DATALENGTH(12) AS [DATALENGTH]

2. Returns size of the input expression in bytes.

3. Trailing blank spaces are considered while determining the size of the input expression. Example, the following will return output 16.

SELECT DATALENGTH('Ashwani Singh   ') AS [DATALENGTH]

The reason for above behavior is because DATALENGTH considers the size of each blank space as one byte.

4. It returns value 1 as the size for Blank space character. Example, the following will return value 1.

SELECT DATALENGTH(' ') AS [DATALENGTH]

Screenshots:

Conclusion
This clearly brings us to conclusion.
1. LEN function must be used when using string datatypes to find the length of the string while DATALENGTH function must be used to determine the size in bytes of any datatype.

2. DATALENGTH can be used for string datatype where trailing blank spaces also needs to be considered.