Both LEN and DATALENGTH function are used to determine lengths.
Similarities between LEN and DATALENGTH function 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 5 for the string “Divya” 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 5 = 5).
SELECT LEN(‘Divya’) AS [LEN]
SELECT DATALENGTH(‘Divya’) AS [DATALENGTH]
Differences between LEN and DATALENGTH functions in SQL Server