T-SQL select 1 where '{' < 'a' why does this return a result, it should be em-CodePudding

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'

CodePudding user response：

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 alphabetically and then uppercase and lowercase, AaBbCc...YyZz. For a binary collation they are uppercase, lowercase and then alphabetically, ABC..YZabc...yz.

IF you want to see the ordering of the letters, you could do something like this for Latin based collations:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1AS I
    FROM N N1, N N2, N N3, N N4)
SELECT T.I,
       V.C
FROM Tally T
     CROSS APPLY(VALUES(CHAR(T.I) COLLATE Latin1_General_CI_AI))V(C) --Replace collation as needed
ORDER BY V.C;