Re: convert accented character to base character

On 28 June 2010 16:33, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> On 28 June 2010 16:22, Neubert Joachim <J(dot)Neubert(at)zbw(dot)eu> wrote:
>> I want to convert accented characters to the according base character, e.g.
>> "Ü" or "Ú" to "U".
>>
>>
>>
>> Is there a way to do this with pgsql functions?
>>
>>
>>
>>
>>
>>   postgres=# select convert('Ü', 'UTF8', 'SQL_ASCII');
>>
>>    convert
>>
>>   ----------
>>
>>    \303\234
>>
>>
>>
>> did not work as I had hoped.
>>
>>
>>
>> Any help would be appreciated -
>>
>>
>>
>> Cheers, Joachim
>
> There's a function called unaccent coming in PostgreSQL 9.0:
> http://www.postgresql.org/docs/9.0/static/unaccent.html
>
> But in the meantime, you could try this:
>
> CREATE OR REPLACE FUNCTION unaccent_string(text) RETURNS text AS $$
> DECLARE
>    input_string text := $1;
> BEGIN
>
> input_string := translate(input_string,
> 'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
> 'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu');

That should actually be
'aaaaaaaAAAAAAAAeeeeeeeeeeEEEEEiiiiiiiiIIIIIIIIoooooooOOOOOOOOuuuuuuuuUUUUUUUU');

>
> return input_string;
> END;
> $$ LANGUAGE plpgsql;
>
> Then you can do:
>
> select unaccent_string('Ü');
>
> Someone else may have a better suggestion though.
>
> Regards
>
> Thom
>