Re: Lower case

On Wed, 26 Jan 2005 12:01:49 +0000, Vladimir S. Petukhov
<vladimir(at)sycore(dot)org> wrote:
> I want to do case-insensitivity search, like this
> ... WHERE lower (column_name) LIKE lower (%value%);
> This work fine for English..
> But i need search for Russian words, lower() operator does not work with
> Russian (non-English) chars, but ORDER works fine...
> ???????????????
> What's wrong?

Welllllll... Have you made an initdb with apropriate locale setting?
Try:
pg_controldata /var/lib/postgresql/data
(or wherever your db is)
You should see lines like:
LC_COLLATE: C
LC_CTYPE: C

If you are using Unicode these should be ru_RU.UTF-8, if not then
ru_RU.KOI8-R or something. If you see 'C', or 'en_EN' or similar
then you won't have Russian lower/upper support (and ORDER BY
was just a "luck" :)).

What you can do:
pg_dump the database, initdb --locale=ru_RU; pg_restore the database.

Ohhh, and since you're at it, there is one thing which makes me
wonder and if you don't mind, please try it. :) This will require
plperlu language, so 'createlang plperlu' and that you use
unicode encoding in your database.

Try if this function:
CREATE OR REPLACE FUNCTION lc(text) RETURNS text AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu STABLE;

Used as select lc(your_text_column) from your_table;
works for Russian alphabet. :) I'm just cuuurious! :)

Regards,
Dawid