Re: LC_CTYPE and matching accented chars

From: Martin Langhoff <martin(at)catalyst(dot)net(dot)nz>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: LC_CTYPE and matching accented chars
Date: 2007-06-26 21:13:02
Message-ID: 4681815E.1020401@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Alvaro Herrera wrote:
> Martin Langhoff wrote:
>> # this is apparently the right way to
>> # select base character based on the "equivalence class"
>> # as defined in the LC_CTYPE
>> =# select * from test where value ~ 'mart[=i=]n';
>
> I think it would be much easier if you did something like
>
> select * from test where lower(to_ascii(value)) = lower(to_ascii('martín'));
>
> When to_ascii doesn't work (for example because it doesn't work in UTF8)
> you may want to use convert() to recode the text to latin1 or latin9.

Well, with the example above to_ascii doesn't work.

select to_ascii(value) from test ;
ERROR: encoding conversion from UTF8 to ASCII not supported

And neither does convert

select convert(value using utf8_to_ascii) from test ;
ERROR: character 0xc3 of encoding "MULE_INTERNAL" has no equivalent
in "SQL_ASCII"

select convert('martín' using utf8_to_ascii) from test ;
ERROR: character 0xc3 of encoding "MULE_INTERNAL" has no equivalent
in "SQL_ASCII"

Any other alternatives?

BTW, this is on Pg8.2 as seen on Ubuntu Feisty PPC.

$ dpkg -l postgresql-8.2
ii postgresql-8.2 8.2.4-0ubuntu0.7.04

cheers,

martín
--
-----------------------------------------------------------------------
Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224 UK: 0845 868 5733 ext 7224 MOB: +64(21)364-017
Make things as simple as possible, but no simpler - Einstein
-----------------------------------------------------------------------

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Martin Langhoff 2007-06-26 21:28:24 Re: LC_CTYPE and matching accented chars
Previous Message Rafal Pietrak 2007-06-26 21:11:33 Re: a JOIN on same table, but 'slided over'