SQL: Do you really know how LEN works in T-SQL?

SQL: Do you really know how LEN works in T-SQL?

I’ve never liked how the LEN function works in T-SQL for SQL Server.

To test out what others thought, I recently posted a short quiz on my social networks. Here’s the question:

What would you expect the output from this query to be?

DECLARE @Value1 varchar(10) = 'Hello   ';
DECLARE @Value2 varchar(10) = 'There ';

SELECT LEN(@Value1), LEN(@Value2), LEN(@Value1 + @Value2);

Nothing tricky. The first value has 3 trailing spaces. The second value has one.

Then I gave the following answer options:

  • 8, 6, 14
  • 5, 5, 10
  • 5, 5, 13
  • 8, 6, 13

Almost no-one got the answer correct. That tells you that the behavior is unexpected.

The answer is C.

It’s because trailing spaces are stored but the LEN function ignores them. Ron Dunn asked an insightful question about what the ANSI_PADDING setting was. In this case, it was at the default value of ON. (Turning it off is now deprecated).

Why is C the answer?

So what’s happening is that the first LEN operates on the string Hello followed by 3 spaces. It ignores the spaces and returns 5. The second LEN operates on the string There followed by one space. It also ignores the spaces and returns 5.

Then the third LEN is operating on the concatenation of the two strings, which is ‘Hello There ‘. That’s a total of 14 characters, but the trailing space is ignored, so it returns 13. (The spaces in the middle are still significant).

So how do you measure the length of a string?

When I’m working with strings, and I want the length, I want all the characters, including any trailing spaces. LEN won’t do that.

The cleanest method I’ve come up with is as follows:

LEN(@ValueToMeasure + '.') - 1

That’s a bit nasty for my liking though. Another option that I’ve heard suggested is:

LEN(REPLACE(@ValueToMeasure, ' ', '.'))

I really wish that T-SQL had a LEN function that worked correctly. My suggestion would be to add CHAR_LENGTH() or STRING_LENGTH() as they already exist in other SQL dialects.

The ones that have CHAR_LENGTH() also have OCTET_LENGTH() for the number of octets (basically bytes), but we do already have DATALENGTH() for that.

(Great Unsplash image from Diana Polekhina thanks)

2026-09-03