Re: convert accented character to base character

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');

return input_string;
END;
$$ LANGUAGE plpgsql;

Then you can do:

select unaccent_string('Ü');

Someone else may have a better suggestion though.

Regards

Thom