Sorting with sql server and collation and unicode.

Question

Hi

I've got a table in sql server, and one of the columns is used to store multilingual data, it's current nvarchar(250).

When sorting the column on the web front end I would like to take the user's locale into account so e.g spanish is sorted correctly taking "ch" into account.

I'm a bit lost in how to do this.

Can anyone point me in the right direction.

Answer 1

If you are using SQL Server 2005 onwards, you can specify a COLLATION name in an ORDER BY clause, so you could construct your query dynamically and choose the collation name:

[ ORDER BY 
    {
    order_by_expression 
  [ COLLATE collation_name ] 
  [ ASC | DESC ] 
    } [ ,...n ] 
]

But be sure to read The Curse and Blessings of Dynamic SQL

Answer 2

SELECT ...
FROM Table
WHERE ...
ORDER BY <column> COLLATE Spanish_CI_AI;

You can specify a specific collation to a column during operations using COLLATE. This act as a cast of the type data to the specified collation.