August 21, 2013 at 6:54 am
Hi,
I tried to sort names in my application.
It is strange that W comes in between V :alien:
Select * from (
Select 'Västerbergslagens Kraft AB' Name
union
Select 'Västerbergslagens Elförsäljning AB' Name
union
Select 'Wäst-Bygg AB' Name
union
Select 'Västbo Galvan AB' Name
union
Select 'Västanvind Vindkraftskooperativ ek för') t
order by t.Name
Actual output:
Name
Västanvind Vindkraftskooperativ ek för
Västbo Galvan AB
Wäst-Bygg AB
Västerbergslagens Elförsäljning AB
Västerbergslagens Kraft AB
Expected:
Name
Västanvind Vindkraftskooperativ ek för
Västbo Galvan AB
Västerbergslagens Elförsäljning AB
Västerbergslagens Kraft AB
Wäst-Bygg AB
Please guide me.
August 21, 2013 at 7:13 am
I can't reproduce it on my instance. What is the collation setting of your instance and your database?
August 21, 2013 at 7:19 am
It is Finnish_Swedish_CI_AS
August 21, 2013 at 7:31 am
pandiyarajk.rjpm (8/21/2013)
It is Finnish_Swedish_CI_AS
With the code below I am able to reproduce the same results. It looks like with this collation the "W" and "V" are treated as the same character. I have added two extra values of the text 'Wäst-Bygg AB' in the query below in which I altered the third character. You see the text starting with the character "W" to be first, fourth and last in the order.
Select * from (
Select 'Västerbergslagens Kraft AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Västerbergslagens Elförsäljning AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Wäst-Bygg AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Wädt-Bygg AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Wätt-Bygg AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Västbo Galvan AB' COLLATE Finnish_Swedish_CI_AS Name
union
Select 'Västanvind Vindkraftskooperativ ek för' COLLATE Finnish_Swedish_CI_AS name) t
order by t.Name
August 21, 2013 at 7:32 am
When I ran your SQL on my machine, it produced the results you expected.
However, the database on which I ran the statement had the collation Latin1_General_CI_AS
When I ran your statement using the COLLATE clause :
Select * from (
Select 'Västerbergslagens Kraft AB' Name
union
Select 'Västerbergslagens Elförsäljning AB' Name
union
Select 'Wäst-Bygg AB' Name
union
Select 'Västbo Galvan AB' Name
union
Select 'Västanvind Vindkraftskooperativ ek för') t
order by t.Name COLLATE Finnish_Swedish_CI_AS
The result was what you had published:
Name
Västanvind Vindkraftskooperativ ek för
Västbo Galvan AB
Wäst-Bygg AB
Västerbergslagens Elförsäljning AB
Västerbergslagens Kraft AB
In order for you to get your SELECT into the order you are expecting, use COLLATE Latin1_General_CI_AS after your ORDER BY clause:
Select * from (
Select 'Västerbergslagens Kraft AB' Name
union
Select 'Västerbergslagens Elförsäljning AB' Name
union
Select 'Wäst-Bygg AB' Name
union
Select 'Västbo Galvan AB' Name
union
Select 'Västanvind Vindkraftskooperativ ek för') t
order by t.Name COLLATE Latin1_General_CI_AS
BUT -- before you do that, are you sure that the results you are expecting are the actual intended results? Since the database is in a different collation, is it possible that the actual output is correct?
( I know nothing about the Finnish_Swedish collation )
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
August 21, 2013 at 7:34 am
it's by design. You will need to change collation or use the collate keyword
August 21, 2013 at 7:57 am
Thanks HanShi for explaining it to me:-)
August 21, 2013 at 7:58 am
Our application supports multiple languages.
I understand that, On viewing in English language, i have to use the collation "Latin1_General_CI_AI"
Collation will be "Finnish_Swedish_CI_AS" for Swedish language.
Thanks ChrisCarsonSQL:-)
August 22, 2013 at 3:10 pm
Just a guess:
It look like the issue is related to the Finnish alphabet, which only uses the "W" for spelling on foreign words. Pure Finnish words apparently don't use the "W", and since the W is pronounced as a "V" the sorting is such that W's are replaced with V's, explaining the order that was returned to you.
August 22, 2013 at 4:13 pm
pandiyarajk.rjpm (8/21/2013)
I tried to sort names in my application.It is strange that W comes in between V :alien:
It's not strange at all! That's how we sort in Swedish(*). In Swedish V and W are pronounced the same and W is just considered a variant of V. On the other hand ÅÄÖ are not variants of A and O, but they are separate letters.
(*) Actually, in the most recent edition of their dictionary, the Swedish Academy separated V and W into separate sections, so maybe a later version of SQL Server will have Finnish_Swedish_140_CI_AS where V and W do not co-sort.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply