sql server string comparison

Question

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

Answer 1

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; 

Answer 2

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