CODEWITHSUNDEEP
sqlsql-server
Jon Hanlon
Jon Hanlon

Reputation: 172

sql server string comparison

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

Upvotes: 5

Views: 357

Answers (2)

Drenmi
Drenmi

Reputation: 8787

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')

http://sqlfiddle.com/#!6/9eecb/1264

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46425

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;

Upvotes: 6

Related Questions