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