If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ?

On SQL Server I get

    case when '' = ' ' then 'true' else 'false' end, 
    case when '' = CHAR(9) then 'true' else 'false' end


---- -----
true false

Is this a problem of the ANSI specification or a problem of the SQL Server implementation?


I do not have access to the text of the specification. I only see it mentioned in this answer. I can't tell if it addresses only blanks or if it refers to whitespace in some vague sense.

  • You have the ANSI 92 text in my answer in case you didn't see. I quote it often...
    – gbn
    Dec 22 '11 at 19:08

The same code above on MySQL 5.5 gives the same result

Tab isn't "space", it is a character.
The same applies to hard/non-breaking space (char(160) in "normal" collations)

The ANSI-92 standard states in 8.2. item 3.a

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad char- acters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any char- acter in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

CS is "collating sequence". I'll assume no collation uses tab or non-breaking spaces as padding characters. So space it is...

Note that LIKE is section 8.5 (I mention this because of your previous question)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.