Collation problem

Hi all.
I have a problem with collation in SQLServer database. My table contains few
rows in field of type char(20),
field and server Collation is set to Croatian_CI_AS (Windows collation).
When I execute query SELECT field FROM table ORDER BY field, return set is
ordered as follows
---
field
---
+
-
7
7-
7+
7+1
71
7-1
72
Why does value 7-1 is placed between values 71 and 72 and not after 7+1.
Is there any way to tell SQLServer to set order of returned resultset in the
same way that windows
would set order of files in explorer. Collation Croatian_CI_AS is important
for me because of
correct order of special Croatian characters.

TomislavTomislav Stilinovi (Tomislav.Stilinovic@.zg.htnet.hr) writes:
> I have a problem with collation in SQLServer database. My table contains
> few rows in field of type char(20), field and server Collation is set to
> Croatian_CI_AS (Windows collation). When I execute query SELECT field
> FROM table ORDER BY field, return set is ordered as follows
> ---
> field
> ---
> +
> -
> 7
> 7-
> 7+
> 7+1
> 71
> 7-1
> 72
> Why does value 7-1 is placed between values 71 and 72 and not after 7+1.

I think most Unicode sorting algorithms with some level of sophistication
considers hyphen to be an ignorable character, at least on primary level.
This is also how you sort in a dictionary.

> Is there any way to tell SQLServer to set order of returned resultset in
> the same way that windows would set order of files in explorer.

No. And it appears that the one that is really the odd one out is
Explorer. I entered your data in Word, and asked it to sort, and I
got the same result as in SQL Server.

I ran my tests with Finnish_Swedish collations, and regional settings
set to Swedish, but I don't think this makes a difference in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

collation in sql 2005

Does anyone know if there is a command in query analizer for sql server 2005 (Express) that changes the collation of all the char (varchar, nvarchar etc) in a table or in a database. And I don't mean to set the collation when creating a database, but to modify a database's collation seting.

thank you

This information might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3zw3.asp

Buck Woody

Collation conflict

I have 2 servers, both of them have the same collation.
Then I create temp table on 1st server:
create table #tmpNovi (country char(3) COLLATE database_default ,datum
datetime)
Then fill the table.
Then I join this table to second server:
select s.* FROM
[SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
ON s.RCO=n.country
and I get an error message:
Cannot resolve collation conflict for equal to operation.
Why? Both servers have the same collation, also temp table country field has
defined COLLATE database_default and still an error?
Thank you,
SimonRun below statement on both servers and post back the results:
SELECT DATABASEPROPERTYEX('pubs', 'Collation')
Substitute pubs with DW_Temp when you execute it on SERVER2 and with the dat
abase name from where
you create the temp table on the other server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum dat
etime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as defined COLLATE
> database_default and still an error?
> Thank you,
> Simon
>|||Dear all,
As far as I know it's very simply: just for that is needed that both level o
f
COLLATION coinciding, i.e, level table, level database.
It's strange.
See you,
"simon" wrote:

> I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum
> datetime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as
> defined COLLATE database_default and still an error?
> Thank you,
> Simon
>
>|||Hi Tibor,
Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
available a similar sentence but at table level?
thanx
"Tibor Karaszi" wrote:

> Run below statement on both servers and post back the results:
> SELECT DATABASEPROPERTYEX('pubs', 'Collation')
> Substitute pubs with DW_Temp when you execute it on SERVER2 and with the d
atabase name from where
> you create the temp table on the other server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>
>|||Try sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:3B49DD33-1160-4069-A161-7A5313738DBC@.microsoft.com...
> Hi Tibor,
> Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
> available a similar sentence but at table level?
> thanx
> "Tibor Karaszi" wrote:
>

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
>
>