Collation and tilde (~)

I have a table with a char(7) column with values like this:
0000100
0000200
ABC
DEF
~000300
~000400
When I use the default SQL Server collation, the values starting with the ~
sort above the "ABC" and "DEF" values (unlike several other database servers
I have used). I want those values to come last when ordering by that
column.
So far, the only collation I have come up with (I've tried several) that
does that is Latin1_General_BIN. But that makes the column case sensitive.
Is there another collation that would sort the tilde last, but provide case
insensitive comparisions on the column?I don't know of a collation which does this, but if sorting the resultset if
your requirement, assuming you have no values with 'ZZZZZZZ', you can just
do:
ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
Anith|||Unfortunately, the program has to work across different database servers, so
we do not want to use SQL Server-specific queries if we really do not have
to.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
> I don't know of a collation which does this, but if sorting the resultset
if
> your requirement, assuming you have no values with 'ZZZZZZZ', you can just
> do:
> ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
> --
> Anith
>|||Hello JJ,
I guess the only way to find the right collation to meet your needs
is to try it out different collations (as you might have already done
it). If you can't, then as Anith
has pointed out you will have to modify the queries to meet the
sorting needs.
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "JJ" <jjjj@.nospam.com>
>References: <O6#ZVHqCEHA.2656@.TK2MSFTNGP12.phx.gbl>
<OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl>
>Subject: Re: Collation and tilde (~)
>Date: Mon, 15 Mar 2004 14:21:21 -0500
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <#$do0KsCEHA.308@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 146.145.51.166
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:333939
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Unfortunately, the program has to work across different database
servers, so
>we do not want to use SQL Server-specific queries if we really do
not have
>to.
>"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
resultset
>if
can just
>
>