Re: Search and Replace

any anyone explain whats wrong with the replace based solution to this problem
which i posted earlier?

did i misunderstood anything?

regds
mallah.

On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote:
> 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'||s
>ubstr(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'||sub
> >str(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(at)rice(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.