T-SQL select 1 where ‘{‘ < ‘a’ why does this return a result, it should be empty
The brace characters have an ASCII value greater then ‘a’. I expected an empty result just like if I did:
select 1 where ‘b’ < ‘a’
>Solution :
As I mention in the comments, the ordering of characters isn’t based on the ASCII values of the characters (123 for { and 97 for a), but the collation.
I don’t know what collation you are using, but in the collation you are using a left brace ({) has a lower value than the characters a. So, perhaps you are using Latin1_General_CI_AI:
SELECT 1 WHERE '{' < 'a' COLLATE Latin1_General_CI_AI;
If you were to be using a binary collation, such as Latin1_General_BIN, then you would not get a result:
SELECT 1 WHERE '{' < 'a' COLLATE Latin1_General_BIN;
Even the orders of letters can be different. Take the following:
SELECT 1 WHERE 'B' < 'a' COLLATE Latin1_General_CS_AS;
SELECT 1 WHERE 'B' < 'a' COLLATE Latin1_General_BIN;
Note that the first statement does not return a result, but the latter does. This is because in Latin1_General_CS_AS letters are ordered Uppercase, lowercase, and then alphabetically, AaBbCc…YyZz. For a binary value then are alphabetically and then upper case, ABC..YZabc…yz.