pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
String REPLACE function
3+ messages / 3 participants
[nested] [flat]

* String REPLACE function
@ 2001-03-22 12:14 Joel Burton <jburton@scw.org>
  2001-03-22 15:19 ` Re: String REPLACE function Bruno Wolff III <bruno@wolff.to>
  2001-03-28 19:16 ` Re: String REPLACE function Martin Jacobs <100.179370@germanynet.de>
  0 siblings, 2 replies; 3+ messages in thread

From: Joel Burton @ 2001-03-22 12:14 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql


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.

Am I missing anything? Has anyone already solved this?

Thanks!

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: String REPLACE function
  2001-03-22 12:14 String REPLACE function Joel Burton <jburton@scw.org>
@ 2001-03-22 15:19 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2001-03-22 15:19 UTC (permalink / raw)
  To: Joel Burton <jburton@scw.org>; +Cc: pgsql-general@postgresql.org; pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: String REPLACE function
  2001-03-22 12:14 String REPLACE function Joel Burton <jburton@scw.org>
@ 2001-03-28 19:16 ` Martin Jacobs <100.179370@germanynet.de>
  1 sibling, 0 replies; 3+ messages in thread

From: 100.179370@germanynet.de @ 2001-03-28 19:16 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-28 19:16 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-22 12:14 String REPLACE function Joel Burton <jburton@scw.org>
2001-03-22 15:19 ` Bruno Wolff III <bruno@wolff.to>
2001-03-28 19:16 ` Martin Jacobs <100.179370@germanynet.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox