Re: LC_CTYPE and matching accented chars

Martin Langhoff wrote:
> Trying to understand how I can get Pg 8.2 to match 'martín' when I
> search for 'martin', and going through the documentation, specially
>
> http://www.postgresql.org/docs/8.2/static/locale.html
> http://www.postgresql.org/docs/8.1/static/functions-matching.html
>
> Here is a transcript of my commandline - I suspect I am quite close, but
> something is missing. Hints _really_ appreciated!

> =# create table test (value text);
> CREATE TABLE
> =# insert into test values ('martín');
> INSERT 0 1
>
> # 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.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support