Re: String REPLACE function

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/