There are few typical questions that developers ask when developing applications or when troubleshooting an issue:

• What is the length of this string?
• How many bytes does this string take for storage?
• How many bytes are occupied in storing this BLOB data?

The answer to all these questions lie in 2 T-SQL functions: The DATALENGTH() and the LEN(), which are often used interchangeably by developers who may not have fully understood the subtle differences between them.

Today, I present before you, a feature comparison of these two functions.

The LEN() function

Purpose: Returns the number of characters of the specified string expression, excluding trailing blanks.

Usage example: The following example demonstrates how to use the LEN() function:

SELECT LEN('Four');
GO
--Result: 4

Behaviour for Unicode Strings: Unicode strings store a wider range of characters and hence the same number of characters occupy more space. The LEN() function reports the number of characters, and not the space occupied within a UNICODE string.

SELECT LEN(N'Four');
GO
--Result: 4

Effect of white spaces: The LEN() function has a very interesting behaviour when the string expression being evaluated is padded with white spaces. The function trims out white spaces after a string, but does consider white spaces padded before the string.

SELECT LEN(' Four');
GO
--Result: 8
SELECT LEN('Four ');
GO
--Result: 4

Behaviour with non-string based data-types: Because the DATALENGTH() function returns the number of bytes consumed, it can work with any expression and return the number of bytes consumed. Here is an example of how DATALENGTH() reports the number of bytes accurately for non-character based data-types:

DECLARE @integerData INT = 4;
SELECT LEN(@integerData);
GO
--Result: 1 (the number of characters)
DECLARE @dataTimeValue DATETIME = GETDATE();
SELECT LEN(@dataTimeValue);
GO
--Result: 19 (the number of characters)

The DATALENGTH() function

Purpose: Check the length of any expression.

Usage example: The following example demonstrates how to use the DATALENGTH() function. From a usage perspective, there is no difference between DATALENGTH() and LEN()

SELECT DATALENGTH('Four');
GO
--Result: 4

Behaviour for Unicode Strings: Unicode strings store a wider range of characters and hence the same number of characters occupy more space. Unlike the LEN() function, the DATALENGTH() function returns the actual number of bytes consumed for the storage of the Unicode value.

SELECT DATALENGTH(N'Four');
GO
--Result: 8

Effect of white spaces: Because the DATALENGTH() function returns the number of bytes consumed, and not the number of characters in an expression, white spaces in a string are reported as-is, without any trimming. Here’s an example:

SELECT DATALENGTH(' Four');
GO
--Result: 8
SELECT DATALENGTH('Four ');
GO
--Result: 8

Behaviour with non-string based data-types: Because the DATALENGTH() function returns the number of bytes consumed, it can work with any expression and return the number of bytes consumed. Here is an example of how DATALENGTH() reports the number of bytes accurately for non-character based data-types:

DECLARE @integerData INT = 4;
SELECT DATALENGTH(@integerData);
GO
--Result: 4 (the number of bytes consumed for storage)
DECLARE @dataTimeValue DATETIME = GETDATE();
SELECT DATALENGTH(@dataTimeValue);
GO
--Result: 8 (the number of bytes consumed for storage)

Summary

The feature comparison between LEN() and DATALENGTH() can be made in the following table:

Further Reading

• LEN function [MSDN Link]
• DATALENGTH function [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.