agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedGerman "umlaut insensitive" query
5+ messages / 4 participants
[nested] [flat]
* German "umlaut insensitive" query
@ 2004-09-01 14:01 Robert Strötgen <robert@stroetgen.de>
2004-09-01 14:36 ` Re: German "umlaut insensitive" query Michael Kleiser <mkl@webde-ag.de>
0 siblings, 1 reply; 5+ messages in thread
From: Robert Strötgen @ 2004-09-01 14:01 UTC (permalink / raw)
To: pgsql-sql
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#AEN21761).
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/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: German "umlaut insensitive" query
2004-09-01 14:01 German "umlaut insensitive" query Robert Strötgen <robert@stroetgen.de>
@ 2004-09-01 14:36 ` Michael Kleiser <mkl@webde-ag.de>
2004-09-01 15:17 ` Re: German "umlaut insensitive" query Robert Strötgen <robert@stroetgen.de>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Kleiser @ 2004-09-01 14:36 UTC (permalink / raw)
To: Robert Strötgen <robert@stroetgen.de>; +Cc: pgsql-sql
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#AEN21761).
>
> 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. :)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: German "umlaut insensitive" query
2004-09-01 14:01 German "umlaut insensitive" query Robert Strötgen <robert@stroetgen.de>
2004-09-01 14:36 ` Re: German "umlaut insensitive" query Michael Kleiser <mkl@webde-ag.de>
@ 2004-09-01 15:17 ` Robert Strötgen <robert@stroetgen.de>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Strötgen @ 2004-09-01 15:17 UTC (permalink / raw)
To: pgsql-sql
> 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/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 5+ messages in thread
* German "umlaut insensitive" query
@ 2019-09-20 08:36 Jakob.Witczak@dlr.de
2019-09-20 12:17 ` Re: German "umlaut insensitive" query Jonathan S. Katz <jonathan.katz@excoventures.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jakob.Witczak@dlr.de @ 2019-09-20 08:36 UTC (permalink / raw)
To: pgsql-sql
Hi,
i found an entry from 2004, where it is about the German Umlaute and i have
the same problem.
<https://www.postgresql.org/message-id/4135E7F5.4070002%40stroetgen.de;
https://www.postgresql.org/message-id/4135E7F5.4070002%40stroetgen.de
I also found this article:
<https://dba.stackexchange.com/questions/195502/postgres-collate-example-in-
select>
https://dba.stackexchange.com/questions/195502/postgres-collate-example-in-s
elect
Are there other possibilities today (for example COLLATE) besides replace()
to process the German Umlaute?
In the long term, we also want to use it for other countries.
Best Regards
Jakob Witczak
Deutsches Zentrum für Luft- und Raumfahrt e.V. (DLR)
Institut für Luft- und Raumfahrtmedizin | Abteilung für Luft- und
Raumfahrtpsychologie | Sportallee 54a | 22335 Hamburg
P.-Jakob Witczak | Dipl.-Ing.(FH)
Telefon 040 513096-845 | <mailto:jakob.witczak@dlr.de> jakob.witczak@dlr.de
<http://www.dlr.de/; DLR.de
Attachments:
[application/pkcs7-signature] smime.p7s (7.6K, ../../242EFA74EBC4CE45AD34310EEB2145E70E498596@DLDEFFMIMP03EXC.intra.dlr.de/3-smime.p7s)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: German "umlaut insensitive" query
2019-09-20 08:36 German "umlaut insensitive" query Jakob.Witczak@dlr.de
@ 2019-09-20 12:17 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jonathan S. Katz @ 2019-09-20 12:17 UTC (permalink / raw)
To: Jakob.Witczak@dlr.de; +Cc: pgsql-sql
> On Sep 20, 2019, at 4:36 AM, <Jakob.Witczak@dlr.de> <Jakob.Witczak@dlr.de> wrote:
>
> Hi,
>
> i found an entry from 2004, where it is about the German Umlaute and i have the same problem.
> https://www.postgresql.org/message-id/4135E7F5.4070002%40stroetgen.de <https://www.postgresql.org/message-id/4135E7F5.4070002%40stroetgen.de;
>
> I also found this article:
> https://dba.stackexchange.com/questions/195502/postgres-collate-example-in-select <https://dba.stackexchange.com/questions/195502/postgres-collate-example-in-select;
>
> Are there other possibilities today (for example COLLATE) besides replace() to process the German Umlaute?
> In the long term, we also want to use it for other countries.
There is a feature in the upcoming PostgreSQL 12 release that
allows for accent insensitive queries:
https://www.postgresql.org/docs/12/collation.html#COLLATION-NONDETERMINISTIC <https://www.postgresql.org/docs/12/collation.html#COLLATION-NONDETERMINISTIC;
This is part of PostgreSQL’s support for ICU collations.
Thanks,
Jonathan
Attachments:
[application/pgp-signature] signature.asc (832B, ../../DA29F2FD-A2DC-4CA0-81D5-283D9A17B548@excoventures.com/3-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-09-20 12:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-01 14:01 German "umlaut insensitive" query Robert Strötgen <robert@stroetgen.de>
2004-09-01 14:36 ` Michael Kleiser <mkl@webde-ag.de>
2004-09-01 15:17 ` Robert Strötgen <robert@stroetgen.de>
2019-09-20 08:36 German "umlaut insensitive" query Jakob.Witczak@dlr.de
2019-09-20 12:17 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox