From: JBJ <postgre@totw.org>
To: pgsql-sql@postgresql.org
Subject: once again, sorting with Unicode
Date: Tue, 18 Feb 2003 23:13:04 +0000
Message-ID: <5.1.0.14.0.20030218224724.02705ff8@localhost> (raw)
Thus is the nature of my problem:
I have a multi-lingual database (currently 11 languages) which sorts fine
in MySQL (8859-1 character set)
I have now converted the data to Unicode and compiled Postgre with unicode
support.
I can select and insert unicode and so was rather pleased about that. Until
I saw that it wasn't working properly when ordering!
I have a table with all 11 languages containing a country list, the webuser
will be able (this is not live yet, MySQL is still running the live stuff
and handling it well) to switch between languages and so I select from a
different column (english, icelandic, russian etc) to display the country
list correctly and do ORDER BY that specific column. I noticed this problem
when the output was Icelandic, and have confirmed it with german and other
languages using non-English characters. The sorting goes all wrong.
All the special letters come in between the A, so we have Þ A Ú A Í A Ó et
cetera. This is wrong, Þ should be the third last letter in the row for
example, not the first and A should be in a right order.
I have is_IS as all the LC_X and so am even more surprised by this.
Furthermore I will have to be able to switch between is_IS and others to
get the correct sort order ( russian sort order different from icelandic
for example, simply due to the nature of the letters) live when the user
selects a different language.
Example is here: http://www.worldfootball.org/sort-test.php
-notice how countries beginning with A are surrounded by various others
starting with non-english letters in the Icelandic section
-in the German section you can see that Österreich comes in between the A
countries
I'm sure this is a simple matter but my googling for the last 3 days has
proved unsuccessful so far.
Feedback greatly appreciated
regards,
JBJ
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgre@totw.org
Subject: Re: once again, sorting with Unicode
In-Reply-To: <5.1.0.14.0.20030218224724.02705ff8@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox