March 30, 2009 at 4:07 pm
Evidently there's a lot for me to learn about sorting. I need to sort by Title(varchar), but the '_' is throwing it out of ASCII order where _ is 95 and 1 is 49.
Case doesn't matter to me, but '1' needs to come before '_'.
When I do:
SELECT Title
FROM dbo.Books
ORDER BY Title ASC
I get:
Title
-----
Pr1
S_Pr
S1_Pr
when I really want:
Title
-----
Pr1
S1_Pr
S_Pr
If anyone can help I'll be extremely grateful!
March 30, 2009 at 4:15 pm
What is your system/database collation? It works fine on my system, collation Latin1_General_BIN2.
March 30, 2009 at 4:23 pm
Is this what you're looking for?
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
After I saw your post, I tried this:
SELECT Title
FROM dbo.Books
ORDER BY Title COLLATE Latin1_General_BIN2 ASC
And it works!!! Is this the way you'd recommend doing it? Is there another better way?
Thanks Lynn!
March 30, 2009 at 4:25 pm
It is one way. I'm still wondering what your collation is on your system. Latin1_General_BIN2 is a case sensitive collation. Your server may be using a case insensitive collation and that may be why it is sorting the way it does.
March 30, 2009 at 4:32 pm
Is there a case-insensitive equivalent? That would actually be even better.
Where can I check the system Collation?
March 30, 2009 at 4:35 pm
Looks like you need to use the Latin1_General_BIN or Latin1_General_BIN2 collation to get the sort order you want. My test code:
select * from (
select 'Pr1' as Title union all
select 'S_Pr' union all
select 'S1_Pr') dt
order by
dt.Title collate Latin1_General_CI_AS
select * from (
select 'Pr1' as Title union all
select 'S_Pr' union all
select 'S1_Pr') dt
order by
dt.Title collate Latin1_General_CS_AS
select * from (
select 'Pr1' as Title union all
select 'S_Pr' union all
select 'S1_Pr') dt
order by
dt.Title collate Latin1_General_BIN2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply