German "umlaut insensitive" query

I want to query words with German "umlauts" (special characters) with
and without normalization. I want to find "grün" (green) written
"gruen" as well.

Using "LIKE" with locale de_DE.iso88591 or .utf-8 does not help (Locale
support should affect "LIKE",
http://www.postgresql.org/docs/7.3/static/charset.html#AEN21 761).

Any Idea how to solve this? Define a special Operator? Has anyone
already done this before?

I am using PostgreSQL 7.3.2 on Linux.

TIA,
Robert Strötgen. :)

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~
Robert Strötgen
mailto:robert@stroetgen.de http://www.stroetgen.de/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: German "umlaut insensitive" query

One solution:

select replace( replace( replace( replace( 'Test ä ö ü ß', 'ä','ae'), 'ö','oe' ), 'ü','ue'), 'ß','ss' );

replace
------------------
Test ae oe ue ss

If you also have upcase-characters, you have to extend the statement.


Robert Strötgen schrieb:
> I want to query words with German "umlauts" (special characters) with
> and without normalization. I want to find "grün" (green) written
> "gruen" as well.
>
> Using "LIKE" with locale de_DE.iso88591 or .utf-8 does not help (Locale
> support should affect "LIKE",
> http://www.postgresql.org/docs/7.3/static/charset.html#AEN21 761).
>
> Any Idea how to solve this? Define a special Operator? Has anyone
> already done this before?
>
> I am using PostgreSQL 7.3.2 on Linux.
>
> TIA,
> Robert Strötgen. :)
>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: German "umlaut insensitive" query

> select replace( replace( replace( replace( 'Test ä ö ü ß', 'ä','ae'),
> 'ö','oe' ), 'ü','ue'), 'ß','ss' );

Thanks a lot. A wrote this into a user defined function with lower()
around the source string, and it works. :-)

CREATE OR REPLACE FUNCTION public.unumlaut(varchar)
RETURNS varchar AS
'select replace( replace( replace( replace( lower($1), \'ä\',\'ae\'),
\'ö\',\'oe\' ), \'ü\',\'ue\'), \'ß\',\'ss\' );'
LANGUAGE 'sql' IMMUTABLE STRICT;

Best regards,
Robert. :)

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~
Robert Strötgen
mailto:robert@stroetgen.de http://www.stroetgen.de/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org