1

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!

Grimm
  • 690
  • 8
  • 17
  • 1
    you will get related details here - https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server – mkRabbani Aug 07 '19 at 09:06

1 Answers1

3

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.

Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
  • 1
    "If processing a unicode string, DATALENGTH will return twice the number of characters." This is not correct. DATALENGTH returns the number of octets (bytes) in a variable. On SQL Server `nvarchar` is a UTF-16 character string and due to Surrogate and Supplementary characters may take four or more bytes to describe single characters. Ref: [Surrogates and Supplementary Characters](https://learn.microsoft.com/en-us/windows/win32/intl/surrogates-and-supplementary-characters) – AlwaysLearning Aug 07 '19 at 09:43