Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
    
Yes thanks. That helps a lot. –  bernd_k Dec 22 '11 at 19:15

1 Answer 1

up vote 1 down vote accepted

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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