Order by Multiple Collate ??

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  May 13, 2006 at 2:47 pm

  #112082

  Hi

  I'm hoping someone can lead me to the right solution.

  I have a system containing multiple languagedata. My big problem is that I can not sort them in alfabetic order depending on the language I want to sort on.

  Ex.

  I have a table "Alfa" with A Column Letter

  The table contains 5 rows

  ö, ä, a, b, å

  Then I need to something like this (will be usp with parameter i the future)

  declare @lang varchar(3)

  set  @lang='Dan'

  select   Letter from  Alfa  order by (case  when @lang= 'Dan' then (Letter collate Danish_Norwegian_CS_AS) else Letter end)

   

  When @lang is Dan The result should be : a, b, ä, ö, å 

  Else The result should be : a, b, å, ä, ö (Finnish_Swedish_CS_AS)

  How do you do it if you would like to sort in other alfabetic orders in the future, multiple COLLATE in Order by doesn't work?.

  regards

  Michael 

   

   

   

   

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  May 15, 2006 at 2:36 am

  #637779

  Hi

  I found a solution that I think didn't cost to much performance

  select Letter from Alfa

  ORDER BY

  (case when @lang= 'Swe' then Letter collate Finnish_Swedish_CI_AS else NULL end)

  ,(case when @lang= 'Dan' then Letter collate Danish_Norwegian_CS_AS else NULL end)

  If someone has a better solution, feel free to tell me

  regards

  Michael