sestell1 (7/27/2012)
SQL Kiwi (7/26/2012)
L' Eomot Inversé (7/25/2012)
Hugo Kornelis (7/11/2012)
In my opinion, it is LEN() that has the strange behaviour here, because it returns the length of the padded version of the string instead of the length of the string itself. This almost caused me to give the wrong answer - but I recalled just in time the number of times I have already been bitten by this strange behaviour of LEN().I don't understand that, Hugo. LEN isn't doing anything with padding except ignoring it. It's the concatenate operation that turns the padding from padding to not padding by putting non-space characters beyond the padding.
+1 I'm not sure what Hugo meant here either, but I agree with you Tom.
I would have expected LEN() to return the length of the string. The fact that it ignores whitespace, but only on one end of the string, seems counter-intuitive to it's intended purpose. RTRIM() exists to remove trailing white space. Why would LEN() implicitly provide this functionality as well?
LEN is just ensuring that two equal strings have equal length: two strings which are the same apart from the number of trailing spaces compare equal in SQL. It would be crazy for LEN to deliver unequal lengths for two equal strings. Of course you may think that the SQL version of string equality is a bit bizarre, but don't blame LEN for that.
Tom