I know it's late, but what's going on here? I'd expect no results as semicolon comes after 9 in ASCII:
select ascii(';') as semicolon, ascii('9') as nine where ';' < '9'
Results:
semicolon nine
59 57
Add a comment
|
2 Answers
Sorting and comparison of character data in SQL Server is determined by code points only with binary collations.
select ascii(';') as semicolon, ascii('9') as nine where ';' COLLATE Latin1_General_BIN < '9' COLLATE Latin1_General_BIN;
With other collations, comparison rules and sort order is dictionary order (special characters before alphas) regardless of the underlying binary code point sequence. Furthermore, Windows collations also follow linguistic "word sort" rules. For example:
SELECT 1 WHERE 'coop' COLLATE SQL_Latin1_General_CP1_CI_AS < 'co-op' COLLATE SQL_Latin1_General_CP1_CI_AS;
SELECT 1 WHERE 'coop' COLLATE Latin1_General_CI_AS < 'co-op' COLLATE Latin1_General_CI_AS;
2 Comments
Jon Hanlon
Thanks Dan, I knew it'd be something esoteric like this. Bloody confusing and obfuscating if you ask me!
Dan Guzman
@JonHanlon, yes it is confusing. Good thing you didn't ask about language and code pages too :-)
When using the < operator on ISO strings, the comparison is made using lexicographical order (i.e. the order you would find in a dictionary.)
http://en.wikipedia.org/wiki/Lexicographical_order
You would need to use the ASCII() function in the WHERE clause for your case to be true.
SELECT ASCII(';') AS semicolon,
ASCII('9') AS nine
WHERE ASCII(';') < ASCII('9')
3 Comments
Jon Hanlon
The ascii() was just to show that semicolon is actually greater than 9. I just want to know why ';' < '9' evaluates to true.
Comfortably Numb
Yes, but why string comparasing yields different result?
Drenmi
When using the
< operator on ISO strings, the comparison is made using lexicographical order (i.e. the order you would find in a dictionary.) I updated my answer to include this information. en.wikipedia.org/wiki/Lexicographical_order