Eric,

The LEN() function ignores trailing spaces by design. It's handy to check if there're only spaces in a column/variable. IOW, LEN(SPACE(10)) = 0
Use DATALENGTH() to get actual size.

>Consider this short query
>
>

>DECLARE @x VARCHAR(20)
>
>SET @x = '1234567890'
>SELECT '-' + @x + '-', LEN(@x), RIGHT(@x, LEN(@x) - 2), RIGHT(RTRIM(@x), LEN(@x) - 2)
>
>SET @x = '1234567890 '
>SELECT '-' + @x + '-', LEN(@x), RIGHT(@x, LEN(@x) - 2), RIGHT(RTRIM(@x), LEN(@x) - 2)
>

>
>Executing this short query gives us this result.
>
>

>---------------------- ----------- -------------------- --------------------
>-1234567890-           10          34567890             34567890
>
>(1 row(s) affected)
>
>
>---------------------- ----------- -------------------- --------------------
>-1234567890 -          10          4567890              34567890
>
>(1 row(s) affected)
>

>
>The second value of @x has a space at the end. Do you see what the LEN function returns in both cases? Do you also see the "interesting" side-effect to the RIGHT function when used with LEN?
>
>That caused me a problem this morning. I wanted to share it with you. If you have any comments, please shoot!