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