Sorting in SQL - I don't get it 1

[DarwinIT]

I got a request that I modify a million applications (maybe a slight exaggeration) to have them all sort a certain field so that all values which start with ( show up at the bottom. Of course they want the top of the list to be ascending. So I decided the best way to do this is replace the () values with something that would perform the same delineation of text but sort the way I want. I chose char(171) and char(187) to do it. But when I sort, even though the decimal value for that character is higher - it sorts above regular letters.
Here's my test code. Can anyone explain why this doesn't sort - and a way I could do this without making changes in a ton of sprocs?

select
'(expired)'
Union
select
char(171) + 'expired' + char(187)
Union
select
'expired'
Union
select
'[expired]'
Union
select
'|expired|'

Union
select
'<expired>'

order by 1

 

[gmmastros]

Have you ever thought about this?

Select 1 Where 'ALPHA' = 'alpha'

What do you expect to get when you run the code above? Most people would say, "1" because comparisons are not case sensitive. Ever wonder why this is?

Can anyone explain why this doesn't sort

It does sort, but not the way you expect it to. Sorting and comparisons are controlled by collations. There is a default collation for the server which is used when you create a new database. There is a default collation for each database for when you create tables and variables. Your default collation sorts the way it does when you run your query. You can use different collations for sorting by using the collate clause, like this:

Select Data
From   (
         select '(expired)' As Data
         Union
         select char(171) + 'expired' + char(187)
         Union
         select 'expired'
         Union
         select '[expired]'
         Union
         select '|expired|'
         Union
         select '<expired>'
       ) As A
order by Data Collate Latin1_General_BIN

and a way I could do this without making changes in a ton of sprocs?

You could try changing the collation for your columns, but if you do this, you should understand the ramifications first. When your collation does not match the default collation for TempDB (which is the same as the default collation for the server), then you are bound to have problems with temp tables and table variables. Trust me when I say, you don't want to be messing around with different collations. It's a nightmare. And you certainly wouldn't want to use a binary collation for your columns.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

So I decided the best way to do this is replace the () values with something that would perform the same delineation of text but sort the way I want. I chose char(171) and char(187) to do it.

You may want to try any of the following Char(208), Char(240), Char(222) or char(254). All 4 of these sort AFTER Z.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

You can see how characters sort for your default collation by running this code in a query window.

Select Number, Char(Number) 
From   Master..spt_values 
Where  Type = 'P'
       And Number Between 1 and 255
Order By Char(Number)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DarwinIT]

I was pretty sure there was a setting on the sort order. You have explained it very nicely. I won't risk altering the collation order on the DB itself. So I guess all the sprocs will have to change, but the change should be minor - just to add the collation property. Thanks so much!!

 

[gmmastros]

Be careful still. Binary collations are case sensitive. With regard to sorting, all the capital letters sort before lower case.

Ex:

Select Data
From   (
         select 'Apple' As Data
         Union All
         select 'apple'
         Union All
         select 'Banana'
         Union All
         select 'banana'
         Union All
         select 'Grape'
         Union All
         select 'grape'
       ) As A
order by Data collate LATIN1_GENERAL_BIN

Results:

[tt][blue]
Apple
Banana
Grape
apple
banana
grape
[/blue][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom