SQL Collation Conundrums

18 Mar 2005 5:41 PM

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
and***@floatingbear.ca

19 Mar 2005 10:33 PM

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

Show quote

"Andrew Butchart" <and***@floatingbear.ca> wrote in message
news:6d5df73f.0503180941.7cd978ac@posting.google.com...
>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
> and***@floatingbear.ca

20 Mar 2005 2:47 PM

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
and***@floatingbear.ca

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message news:<eVmuvONLFHA.2764@tk2msftngp13.phx.gbl>...
> 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" <and***@floatingbear.ca> wrote in message
> news:6d5df73f.0503180941.7cd978ac@posting.google.com...
> >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
> > and***@floatingbear.ca