Re: Search and Replace

On Wed, Jan 08, 2003 at 09:02:47AM -0500, Randy D. McCracken wrote:
> I apologize for having to resort to sending what is most likely a simple
> tech support question regarding PostgreSQL to this list but I have not
> been able to find the answer in the documentation.

Hey, this is Open Source: that's what the mailing lists are for. The only
concern would be is this the right list? I'd suggest that this should probably
be over in NOVICE, but at least you didn't post to HACKERS ;-)



As you've discovered, standard SQL text processing functions are a bit
primitive - usually you break out to the application language for that
sort of thing.  However, if you know for sure that there's only one
instance of the replace string, and it's a fixed length string,  you
can get away with something like this:


test=# select * from pubs;id |              url               
----+-------------------------------- 1 | http://www.srs.fs.fed.us/pub/1 2 | http://www.srs.fs.fed.us/pub/2 3 | http://www.srs.fs.fed.us/pub/3
(3 rows)

test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17);
UPDATE 3

test=# select * from pubs;id |               url                
----+---------------------------------- 1 | http://www.srs.fs.usda.gov/pub/1 2 | http://www.srs.fs.usda.gov/pub/2 3 | http://www.srs.fs.usda.gov/pub/3
(3 rows)

You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history:

select strpos(url,'www.srs.fs.usda.gov') from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;

Hope this helps,

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx./rice.edu      fax: 713-348-6182
Rice University MS-39
Houston, TX 77005