Received: from mx02.nexgo.de (mx02.nexgo.de [151.189.8.97]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2T05dU29463 for ; Wed, 28 Mar 2001 19:05:39 -0500 (EST) (envelope-from mjacobs@germanynet.de) Received: from Schnecke.Windsbach.de (pp.132.19.mue.germanynet.de [151.189.132.19]) by mx02.nexgo.de (Postfix) with SMTP id B0850858A9 for ; Thu, 29 Mar 2001 02:05:38 +0200 (CEST) Received: from localhost (2476 bytes) by Schnecke.Windsbach.de via sendmail with P:stdio/R:GermanyNet/T:GerSmtp (sender: ) (ident using unix) id for ; Wed, 28 Mar 2001 21:16:54 +0200 (MEST) (Smail-3.2.0.104 1998-Nov-20 #16 built 1999-Jan-31) Message-Id: Subject: Re: String REPLACE function In-Reply-To: from Joel Burton at "Mar 22, 2001 07:14:51 am" To: pgsql-general@postgresql.org Date: Wed, 28 Mar 2001 21:16:54 +0200 (MEST) X-Mailer: ELM [version 2.4ME+ PL60 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit From: 100.179370@germanynet.de (Martin Jacobs) X-Archive-Number: 200103/1324 X-Sequence-Number: 6888 Hi Joel, Joel Burton schrieb: > > Is there a function for substring replacement? > > There's translate(s, a, b), but that replaces all characters in a with > their corresponding character in b, eg. > > replace ('this is a cat', 'cat', 'dog') => ghis is o dog > > I'm looking for a function that matches the whole string and replaces it: > > replace ('this is a cat', 'cat', 'dog') => this is a dog > > I know I could write it in PL/PGSQL, but it seems that it would be very > inefficient. We're not using PL/perl or PL/tcl in this project, so I'd > rather not do it this way if it coulod be avoided. As long as you rebuild this functionality with native SQL and/or PGSQL functions performance should not be a problem at all. Have a look at this code example which uses plpgsql as language. CREATE FUNCTION stuff (text, text, text) RETURNS text AS ' DECLARE source ALIAS FOR $1; search ALIAS FOR $2; newstr ALIAS FOR $3; prefix text; postfix text; pos integer; len_of_search integer; BEGIN pos := position(search in source); IF pos <= 0 THEN RETURN source; END IF; len_of_search := char_length(search); prefix := substring(source from 1 for pos - 1); postfix := substring(source from pos + len_of_search); RETURN textcat(textcat(prefix, newstr), postfix); END; ' LANGUAGE 'plpgsql' I have made a very simple test to give you an idea of the performance. I ran update dt set d = stuff(d, 'dog', 'cat') where d notnull; on a table with 4096 records of 'this is a dog'. With replacement output of time psql test -c "update dt set d = stuff(d, 'dog', 'cat') where d notnull; " realtime was about 5.8s, without replacement about 1.9s. This on my now ancient Pentium clone (200MHz IDT Winchip, 72MB RAM). I think this is not so bad for interpreted PL. > > Am I missing anything? Has anyone already solved this? > > Thanks! Hope this helps. > ... Martin -- Dipl-Ing. Martin Jacobs * Windsbach * Germany Registered Linux User #87175, http://counter.li.org/