Unicode and sort order...

This may seem stupid to you db gurus out there, but I cannot work this one out:

Using Unicode we can store any kind of characters in the database and that's fine. But how do we sort the stored data? In my world this is dictated by the collation, but what collation to use with Unicode data? Is there an über sort order for all characters - I don't think so.

Example:

If I use Latin1_General_CI_AS as collation and store Swedish characters SO Win will sort this like this:

A, Å, Ä, B, C etc

when it should be

A, B, C....Z, Å, Ä, Ö

If I use Finnish_Swedish_CI_AS collation instead it will sort right for me, but will instead sort other character sets wrongly.

Ticking "Perform translation of character data" does not help here.

Is there a way to sort all character sets correctly or is that just plain impossible?

/Jan

Re: Unicode and sort order...

 Jan Bonde (Generate Business Sverige AB) wrote:


Is there a way to sort all character sets correctly or is that just plain impossible?

/Jan


The trick here is knowing what "correctly" is to you...

Perhaps you would have better luck using the Sami_Sweden_Finland_100 collation

(saw this by reading finnish-swedish-collation-does-not-handle-sorting-correctly)

Hope this helps.
Af: Tony Yates 22. feb 2010

Re: Unicode and sort order...

Well, as I wrote; "Is there an über sort order for all characters - I don't think so.", so I'm pretty much aware that what is "correct" is a minefield...

Interesting link.

Af: Jan Bonde 23. feb 2010

RE: Unicode and sort order...

Hi colleagues, 

if I understand correctly Database collation as a result sorting should be the same on both Win and Web Clients. 

We have situation where we use Database collation: Finnish_Swedish_CI_AS on the same server

In Web client sorts correctly A, B, C....Z, Å, Ä, Ö

In Win client sorts A, Å, Ä, B, C

Is it possible to adjust this with Windows client settings?

Af: Lina Paulauskaitė 5. apr 2017

RE: Unicode and sort order...

Depends on where in the application you are.
In Win, very little is sorted by the db. Mostly, sorting happens in our string class used by the client. It does however try to follow the culture settings in the control panel - as  a rule - but I believe there are exceptions.

With regard to archives, all netserver based archives can be configured to sort by the db but this will only occur during the loading phase. Once an archive is loaded, any change made to which column to sort by will happen in the GUI with in-memory data.

Bottom line, there is no easy answer...
Conrad

Af: Conrad Weyns 5. apr 2017

RE: Unicode and sort order...

Hi!

 

What shall the recommendation be for customers that have web and win?

Af: Emilija Vilija Treciokaite 10. apr 2017

RE: Unicode and sort order...

My Regional setting in the control panel is "Norwegian Bokmål (Norway).
Win then sorts: a A b B c C d D ... æ Æ ø Ø å Å which looks ok to me. This is regardless fo what the db does. I expect the db must then have a compatible collation for this to be identical on the Web client.
What happens with other cultures on same settings is probalby what you want a universal solution for. I can't help here, sorry!
/conrad

Af: Conrad Weyns 10. apr 2017

RE: Unicode and sort order...

After I changed Region on a client's computer to Swedish it started sorting in windows client correctly also. 

Af: Emilija Vilija Treciokaite 13. apr 2017