Thread: search and replace

I have a database with text in it (articles) and in the article I have <img>
tags for pictures in HTML. I want to go through the DB and replace the urls
of the images. How would I go about doing that? I want to grab all the text
from the articles table and replace the string "http://dopey.lulupress.com"
with "http://faramir/".
Any hints?
-William

[ GoRobotics.net | RobotGeeks.com | RobotDirectory.org ]
Amateur robotics for the masses. Enter the world of robotics
------------------------------------------------------------
"...God maintains a delicate balance between keeping his
existence sufficiently evident so people will know He's there
and yet hiding his presence enough so that people who
want to choose to ignore him can do it "
 - J.P. Moreland PH.D.
----------------------------------------------------------------
Carpe Aeternum - Seize Eternity

Gallamine,

> I have a database with text in it (articles) and in the article I have <img>
> tags for pictures in HTML. I want to go through the DB and replace the urls
> of the images. How would I go about doing that? I want to grab all the text
> from the articles table and replace the string "http://dopey.lulupress.com"
> with "http://faramir/".
> Any hints?

Here's what I did:

1) compiled Postgres with Perl support (--with-perl)
2) createlang plperl (from the command line, as postgres)
3) Created this simple Perl function:

CREATE FUNCTION strswap(
    TEXT, VARCHAR, VARCHAR )
RETURNS TEXT AS '
my($the_text, $look_up, $replace_with) = @_;
$the_text =~ s:$look_up:$replace_with:eg ;
return $the_text;
' LANGUAGE 'plperl' WITH (ISCACHABLE, ISSTRICT);

Then you can use strswap to swap all of this stuff out.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco