1+1=3? Space characters in nvarchar variables and string lengths

Question

I've just stumbled upon this:

Why doesn't the following code:

DECLARE @s nvarchar(10) = N' '

PRINT CONCAT('#', @s, '#')
PRINT CONCAT('#', LEN(@s), '#')

result in either the output

##
#0#

or

# #
#1#

On a SQL Server 2017, however, this code produces the output

# #
#0#

Which seems contradictory to me.

Either the string has the length 0 and is '' or the length 1 and is ' '.

The whole thing becomes even stranger if you add the following code:

DECLARE @s nvarchar(10) = N' '

PRINT CONCAT('#', @s, '#')
PRINT CONCAT('#', LEN(@s), '#')

DECLARE @l1 int = LEN(CONCAT('#', @s, '#'))

PRINT LEN(@s)
PRINT LEN('#')
PRINT @l1

Which outputs the following:

# #
#0#
0
1
3

So we have three substrings, one with length 0, two with length 1. The total string then has length 3? I'm confused.

If you fill @s with several spaces, it looks even more funny - e.g. 5 spaces results in this output:

#     #
#0#
0
1
7

So here's 1×0 + 2×1 even 7. I wish my bank would calculate my account balance like this.

Can someone explain to me what's going on?

Many thanks for your help!

Answer 1

LEN

Returns the number of characters of the specified string expression, excluding trailing spaces.

So LEN(' ') = 0 (only spaces), but LEN(' x') = 2 (no trailing spaces).

LEN excludes trailing spaces. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters.