1

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

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

yields

---- -----
true false

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

Edit:

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.

Community
  • 1
bernd_k
  • 12,369
  • 24
  • 79
  • 111

1 Answers1

1

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)

Community
  • 1
gbn
  • 70,237
  • 8
  • 167
  • 244