Re: Search and Replace
От | Rajesh Kumar Mallah. |
---|---|
Тема | Re: Search and Replace |
Дата | |
Msg-id | 200301091100.32667.mallah@trade-india.com обсуждение исходный текст |
Ответ на | Re: Search and Replace ("Randy D. McCracken" <rdm@srs.fs.usda.gov>) |
Ответы |
Re: Search and Replace
|
Список | pgsql-sql |
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@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 > > ---------------------------(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.
В списке pgsql-sql по дате отправления: