Re: how to ignore accents?

On Thu, Mar 31, 2005 at 08:38:02PM +0200, Ennio-Sr wrote:
> * Michael Fuhr <mike(at)fuhr(dot)org> [300305, 17:42]:
> >
> > CREATE FUNCTION unaccent(text) RETURNS text AS $$
> > BEGIN
> > RETURN translate($1, '\342\347\350\351\352\364\373', 'aceeeou');
> > END;
> > $$ LANGUAGE plpgsql IMMUTABLE STRICT;
>
> Thank you, Michael, for correcting my pessimism (and ignorance ... :-).
> Is there any chance to have that work with PostgreSQL-7.4.7-2 (the
> latest available from Debian/sarge)?
> I put those instructions in a .sql file, did:
> mydb=> \i unaccent.sql
> and got:
> psql:unaccent.sql:3: ERROR: syntax error at or near "$" at character 50

The example uses 8.0's dollar quoting; here's something that should
work in earlier versions:

CREATE FUNCTION unaccent(text) RETURNS text AS '
BEGIN
RETURN translate($1, ''\342\347\350\351\352\364\373'', ''aceeeou'');
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

As I mentioned in another message, I tested with the SQL_ASCII
encoding, so if you use a different encoding then you might need
to make some changes -- the point is that you can use translate()
to convert one character to another (e.g., an accented character
to its unaccented equivalent). You'll probably also want to add
more characters to the translation strings -- they're just short
examples for demonstration purposes.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/