CODEWITHSUNDEEP
sql-serversql-order-bycollationcase-sensitive
Olivier Faucheux
Olivier Faucheux

Reputation: 2606

ORDER BY ... COLLATE in SQL Server

Under SQL Server. A table contains some text with different cases. I want to sort them case-sensitive and thought that a COLLATE in the ORDER BY would do it. It doesn't. Why?

CREATE TABLE T1 (C1 VARCHAR(20))
INSERT INTO T1 (C1) VALUES ('aaa1'), ('AAB2'), ('aba3')

SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CS_AS
SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CI_AS

Both queries return the same, even if the first one is "CS" for case-sensitive 

aaa1
AAB2
aba3

(in the first case, I want AAB2, aaa1, aba3)

My server is a SQL Server Express 2008 (10.0.5500) and its default server collation is Latin1_General_CI_AS.

The collation of the database is Latin1_General_CI_AS too.

The result remains the same if I use SQL_Latin1_General_CP1_CS_AS in place of Latin1_General_CS_AS.

Upvotes: 13

Views: 29544

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294487

Because that is the correct case sensitive collation sort order. It is explained in Case Sensitive Collation Sort Order why this is the case, it has to do with the Unicode specifications for sorting. aa will sort ahead of AA but AA will sort ahead of ab.

Upvotes: 11

Martin Smith
Martin Smith

Reputation: 454020

You need a binary collation for your desired sort order with A-Z sorted before a-z.

SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_bin

The CS collation sorts aAbB ... zZ

Upvotes: 16

Related Questions