String REPLACE function

On Thu, Mar 22, 2001 at 07:14:51AM -0500,
Joel Burton <jburton@scw.org> wrote:

Is there a function for substring replacement?

[snip]

Am I missing anything? Has anyone already solved this?

I asked a similar question about a month ago, and got someone to add doing
sed like string replacement in a function on the todo list.

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/