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!
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com