Tony Stark Tony Stark - 1 month ago 7
SQL Question

specified "COLLATE Latin1_General_CS_AS", query result still "case insensitive"

I want to only selects rows from a table where the last name of the employees stands with a lowercase letter. However, when I run the query below

SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS LIKE '[a-z]%';


it returned all rows where the employees' last name starts with either lowercase letter or uppercase letter.

I have already specified that the query should be case sensitive with
COLLATE Latin1_General_CS_AS
, how come it is not filtering out last name that starts with capital letter?

Could someone please help me out? Thanks in advance for any help!

Answer

According the SQL Server Books Online, the characters included in range searches depend on the sorting rules of the collation. Collation Latin1_General_CS_AS uses dictionary order so both upper and lower case characters of the specified range are included.

Specify a binary collation to get the behavior you want (code point range):

SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_BIN LIKE '[a-z]%';