In this article I will explain with an example, 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 “Mudassar Khan” 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('Mudassar Khan') AS [LEN]
SELECT DATALENGTH('Mudassar Khan') 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(25) 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('Mudassar Khan   ') 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(25) 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('Mudassar Khan   ') 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]
 
 
Screenshot
Similarities and differences between LEN and DATALENGTH functions in SQL Server
 
 
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.