From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Randy D. McCracken <rdm@srs.fs.usda.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: Search and Replace
Date: Wed, 8 Jan 2003 10:18:45 -0600
Message-ID: <20030108161844.GA11190@wallace.ece.rice.edu> (raw)
In-Reply-To: <Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
References: <Pine.LNX.4.44.0301081159400.13271-100000@matrix.gatewaynet.com>
<Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
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 ;-)
<snip description of needing a simple string replace>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, rdm@srs.fs.usda.gov
Subject: Re: Search and Replace
In-Reply-To: <20030108161844.GA11190@wallace.ece.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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