L' Eomot Inversé (7/27/2012)
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.
Ah, but LEN() is not comparing strings, it's supposedly returning an individual string's length... except it doesn't. It rtrims the string first, then returns the length of the result. In my opinion, the behavior of string comparitors in regard to padding shouldn't have anything to do with the LEN() function returning the actual length of a string, regardless of the characters the string is composed of.
Interestingly when two strings are compared (except with LIKE), the strings are padded out to the same length before comparing, not trimmed.