ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegr oups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
> Kaz
>
|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen
|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegrou ps.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>
|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen
|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegr oups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>