Why does t-sql's LEN() function ignore the trailing spaces?

Question

Description of LEN() function on MSDN : Returns the number of characters of the specified string expression, excluding trailing blanks.

Why was the LEN() function designed to work this way? What problem does this behaviour solve?

Related :

• LEN function not including trailing spaces in SQL Server
• charindex() counts whiteshars in the end, len() doesn't in T-SQL

Answer 1

It fixes the automatic padding of strings due to the data type length. Consider the following:

DECLARE @Test CHAR(10), @Test2 CHAR(10)
SET @Test = 'test'
SET @Test2 = 'Test2'
SELECT  LEN(@Test), LEN(@Test + '_') - 1, LEN(@Test2), LEN(@Test2 + '_') - 1

This will return 4, 10, 5 and 10 respectively. Even though no trailing spaces were used for @Test, it still maintains its length of 10. If LEN did not trim the trailing spaces then LEN(@test) and LEN(@Test2) would be the same. More often than not people will want to know the length of the meaningful data, and not the length of the automatic padding so LEN removes trailing blanks. There are workarounds/alternatives where this is not the required behaviour.