Re: Search and Replace - Mailing list pgsql-sql

Just to close off another thread and to give a tad more information...

I was not clear enough in my initial question to the list because not all
of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so
what I was really looking for was the syntax for replacing
"www.srs.fs.fed.us" with "www.srs.fs.usda.gov" and not touching any
records do not contain "www.srs.fs.fed.us"

Ross Reedstrom was kind enough to give me some additional help that worked
perfectly and after doing a few tests I am happy to share his SQL
statement with the list.

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)
where url ~ 'www.srs.fs.fed.us'

Thanks Ross!

--rdm


On Wed, 8 Jan 2003, Ross J. Reedstrom wrote:

>
> <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
>
>