SQL Collation Conundrums

[Andrew Butchart]

I have had some problems with a web site that I am working on. The
DBA set up all of the tables using the default collation sequence of
SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
returned is "out of order" according to my ASP code. For example, ASP
believes that the string "6\" is greater than "6001001" whereas SQL
returns the "6\" record first.

Is there any way to set the default comparison between string values
in ASP to match the order that SQL is returning, or can I update my
query to give me a collation sequence that ASP will like? Changing
the collation sequence in the database isn't a viable option.

Thanks

Andrew Butchar
(e-mail address removed)

 

[John Bell]

Hi

You should be able to use a binary collation to get SQL Server to work like
your ASP

SELECT col1
FROM (
SELECT '6\' COLLATE Latin1_General_BIN AS Col1
UNION ALL SELECT '6001001' ) A
order by col1

To do the opposite in your ASP code I think you would have to write a
function that does character by character comparisons.


John

 

[Andrew Butchart]

Dandy - that's it! I just used the COLLATE on my Order By statement
and it's exactly what I needed.

Many thanks John

Andrew Butchart
(e-mail address removed)