On 28 June 2010 16:22, Neubert Joachim <J.Neubert@zbw.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