Collation Sort order

Question

Collation Sort order

andrewkane17

SSCertifiable

Points: 7529
More actions
April 9, 2007 at 3:53 pm

#153515

We are changing the collation of one of our production systems to SQL_Latin1_General_CP1_CI_AI, this will provide accent insesitivity which will return data for our Canadian clients. So a search for Québec Railway will return a result whether it is Québec Railway or Quebec Railway.
What I need to get for the client is a definition of the sort order that a query would return, what is the sort precedent. Does anybody know of a chart that would list how characters would be sorting?

Thanks in advance

Andrew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

Since you're using Latin1, case insensitive, and accent insensitive, is this what you're looking for? It's not MS SQL Server, but I didn't find anything obviously wrong with it. Alternatively, you could loop through all of the characters in question (using CHAR(xx)), inserting them into a table, and then try sorting the contents. It's been a long time since I've had to deal with any localization issues, but that should get you started.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

David,

Thanks for the reply, looked on google and forums yesterday and only got how to change collations etc. This should give me what I need.

Cheers,

Andrew

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

I am a bit late but what you need is called collation precedence in 2005 it is very easy because you can add the collation to you DMLs. Try the links below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms179886.aspx

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

Kind regards,
Gift Peddie