While I was writing a stored procedure that did some string manipulation and was debugging some unexpected behavior, I came across a fascinating, infurating, hidden-in-plain-sight "feature" of the LEN function: it returns the length of a string expression excluding trailing blanks. So, for example:

declare @string varchar(10)
set @string = '123456789 '

declare @len int
set @len = len( @string )

print @len
print right( @string, @len - 5 )

returns 9 and '789 ', not the expected 10 and '6789 '. Also, with this code:

declare @string varchar(10)
set @string = '123456789 '

declare @len int, @reverse_len int
set @len = len( @string )
set @reverse_len = len( reverse( @string ) )

print @len
print @reverse_len

One might expect that the length of a string would be the same forwards and backwards, but it isn't! @len = 9 and @reverse_len = 10!

A workaround for non-Unicode character data is to use the DATALENGTH function, which returns the size of an expression in bytes. If you choose to use DATALENGTH with Unicode data, you must divide by 2 to get the actual string length. Another workaround would be to append a non-blank character to the end of the string, get the length, then subtract one. Neither is very nice. If we had wanted to exclude trailing blanks, we could have called RTRIM before LEN ourselves.