Message-Id: <200302191049.h1JAnT3a007870@tksoft.com>
Subject: Re: once again, sorting with Unicode
To: postgre@totw.org (JBJ)
Date: Wed, 19 Feb 2003 02:49:28 -0800 (PST)
Cc: pgsql-sql@postgresql.org
Reply-To: tjk@tksoft.com (Troy K.), <tjk@tksoft.com>
In-Reply-To: <no.id> from "JBJ" at Feb 18, 2003 11:13:04 PM
From: "Troy" <tjk@tksoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

JBJ,

The cause for the different values is the fact that unicode=20
characters have different numeric values from ISO8859-1 and other=20
encodings. Only ascii values are in sync with unicode numeric=20
values. This I am sure you knew.

Myself, I've only had to deal with data in different encodings,
not unicode. I've implemented functions to assist in lower case=20
conversions and comparisons for the different encodings. The same=20
would work for unicode as well, you'd just need different functions
for each encoding. Remember that some European scripts have
multi character letters (e.g. "ll," "ch" etc.), so reordering characters
alone would never work. Sorting multibyte data (e.g. Japanese), where=20
you need to use dictionaries, would of course be a much more complex task.

Frankly, I haven't looked at the latest changes to postgres
in terms of encoding related functions, since there hasn't been the need
for me. So all this could be outdated info. I'll leave judgement to someone
whose looked at the features more recently.

Anyway, if a solution doesn't exist already, one solution I would
like would be to have built in helper functions for each encoding,
with unicode as input data. This way one could sort the data in=20
different ways by switching the function.=20
E.g. "... order by sortval_is_is(countryname)" could sort unicode data
in Icelandic order by reading the unicode data and then returning
an integer value based on the meaning of the characters in Icelandic.


Troy


>=20
> Thus is the nature of my problem:
>=20
> I have a multi-lingual database (currently 11 languages) which sorts fine=
=20
> in MySQL (8859-1 character set)
> I have now converted the data to Unicode and compiled Postgre with unicod=
e=20
> support.
>=20
> I can select and insert unicode and so was rather pleased about that. Unt=
il=20
> I saw that it wasn't working properly when ordering!
>=20
> I have a table with all 11 languages containing a country list, the webus=
er=20
> will be able (this is not live yet, MySQL is still running the live stuff=
=20
> and handling it well) to switch between languages and so I select from a=
=20
> different column (english, icelandic, russian etc) to display the country=
=20
> list correctly and do ORDER BY that specific column. I noticed this probl=
em=20
> when the output was Icelandic, and have confirmed it with german and othe=
r=20
> languages using non-English characters. The sorting goes all wrong.
>=20
> All the special letters come in between the A, so we have =DE A =DA A =CD=
 A =D3 et=20
> cetera. This is wrong, =DE should be the third last letter in the row for=
=20
> example, not the first and A should be in a right order.
>=20
> I have is_IS as all the LC_X and so am even more surprised by this.=20
> Furthermore I will have to be able to switch between is_IS and others to=
=20
> get the correct sort order ( russian sort order different from icelandic=
=20
> for example, simply due to the nature of the letters) live when the user=
=20
> selects a different language.
>=20
> Example is here: http://www.worldfootball.org/sort-test.php
> -notice how countries beginning with A are surrounded by various others=
=20
> starting with non-english letters in the Icelandic section
> -in the German section you can see that =D6sterreich comes in between the=
 A=20
> countries
>=20
> I'm sure this is a simple matter but my googling for the last 3 days has=
=20
> proved unsuccessful so far.
>=20
> Feedback greatly appreciated
>=20
> regards,
> JBJ
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>=20