SQL Server LEN vs. DATALENGTH: Spaces, Bytes and Limits

In SQL Server, use LEN(value) for a character count that excludes trailing spaces, and DATALENGTH(value) for the expression’s byte count. A small LEN result does not by itself show that text fits a column: the destination type and collation matter.

Compare the same value in two types

The following example puts an ordinary trailing space after A. The brackets in the table describe the input; they are not part of it. Microsoft’s LEN reference explains the trailing-space rule.

These ASCII examples use a single-byte or UTF-8 collation for varchar. The trailing space remains in the value even though LEN leaves it out of the result. Do not remove spaces from production data merely to make two counts agree.

DECLARE @v varchar(10) = 'A ';
DECLARE @n nvarchar(10) = N'A ';

SELECT LEN(@v) AS v_characters,
       DATALENGTH(@v) AS v_bytes,
       LEN(@n) AS n_characters,
       DATALENGTH(@n) AS n_bytes;

Keep byte counts attached to their expression

DATALENGTH measures the expression supplied to it, including trailing spaces in these values. It returns NULL for a NULL input. A NULL result is therefore different from a measured zero-byte string.

Casting or converting the value changes what you measure. If an import starts as Unicode text but ends in a varchar column, the incoming expression’s byte count is not automatically the destination’s byte count. Conversion can also lose or truncate content. Inspect the intended destination representation and compare the text before and after conversion.

Read the column size in the correct unit

In varchar(n), n sets a byte capacity. SQL Server 2019 and later support UTF-8 varchar storage with a UTF-8 enabled collation. Text outside ASCII can then need several bytes per character. A varchar(10) declaration does not promise room for ten arbitrary Unicode characters.

In nvarchar(n), n counts byte-pairs. A supplementary character uses two such pairs in UTF-16. Dividing DATALENGTH by two counts byte-pairs for nvarchar text; it does not produce a general count of displayed characters.

Check collation before interpreting emoji

With supplementary-character-aware collations, LEN counts a UTF-16 surrogate pair as one. Without that support, the pair can be counted as two. Microsoft’s collation comparison documents this difference. Neither result should be treated as a universal count of visible emoji: a displayed sequence can contain several code points.

Diagnose a rejected value without editing it first

1. Record the destination type, declared size and collation.
2. Keep the original value and inspect trailing spaces rather than silently trimming it.
3. Read LEN and DATALENGTH together, naming their units.
4. Check the destination conversion separately and confirm that it preserves the intended text.

The String Length guide separates characters, code units and bytes. For other engines, use the dedicated PostgreSQL varchar guide or SQLite length guide; their rules do not establish SQL Server behavior.