X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1687E5E40BA for ; Wed, 1 Sep 2004 12:17:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09847-10 for ; Wed, 1 Sep 2004 15:17:15 +0000 (GMT) Received: from onos.eremos.boltzendahl-stroetgen.de (a97.shuttle.de [194.95.224.97]) by svr1.postgresql.org (Postfix) with ESMTP id 251875E471D for ; Wed, 1 Sep 2004 12:17:06 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by onos.eremos.boltzendahl-stroetgen.de (Postfix) with ESMTP id 876EA1AAF8 for ; Wed, 1 Sep 2004 17:17:11 +0200 (CEST) Received: from onos.eremos.boltzendahl-stroetgen.de (localhost [127.0.0.1]) by localhost (AvMailGate-2.0.2-8) id 04386-221D478A; Wed, 01 Sep 2004 17:17:11 +0200 Received: from gorchfock.eremos.boltzendahl-stroetgen.de (gorchfock.eremos.boltzendahl-stroetgen.de [192.168.1.1]) by onos.eremos.boltzendahl-stroetgen.de (Postfix) with ESMTP id 5685DF342 for ; Wed, 1 Sep 2004 17:17:11 +0200 (CEST) Received: from gorchfock.eremos.boltzendahl-stroetgen.de ([192.168.1.1]) by gorchfock.eremos.boltzendahl-stroetgen.de with esmtp (Exim 4.41) id I3DBSO-000238-2C for pgsql-sql@postgresql.org; Wed, 01 Sep 2004 17:17:12 +0200 Message-ID: <4135E7F5.4070002@stroetgen.de> Date: Wed, 01 Sep 2004 17:17:09 +0200 From: =?ISO-8859-1?Q?Robert_Str=F6tgen?= User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: de, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: German "umlaut insensitive" query References: <4135D647.4060203@stroetgen.de> <4135DE5B.2000805@webde-ag.de> In-Reply-To: <4135DE5B.2000805@webde-ag.de> X-Enigmail-Version: 0.84.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-AntiVirus: checked by AntiVir MailGate (version: 2.0.2-8; AVE: 6.27.0.6; VDF: 6.27.0.43; host: onos) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/19 X-Sequence-Number: 18865 > 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/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~