SQL Server LEN function return 0 passing a string with one space

Try to answer this riddle:

What is the result of this SQL Server query:

PRINT LEN(' ')

I would answer 1, since I passed a string with one space…. but it’s the wrong answer.

In this case the LEN function returns 0!

Why this behavior? If you check the Sql Server documentation at

this is explicitly stated:

“Returns the number of characters of the specified string expression, excluding trailing blanks.”

So pay attention to this behavior, because you can easily slip into subtle bugs.

So to get the real string length considering the trailing space, you could do the following:

PRINT LEN(' ' + 'END') - 3

Now the result you obtain is, as expected, 1.

PS: one last curiosity…. in MySql this equivalent istruction:

SELECT LENGTH(' ')

returns 1.

Related Posts