Re: Search and Replace
От | Ross J. Reedstrom |
---|---|
Тема | Re: Search and Replace |
Дата | |
Msg-id | 20030108161844.GA11190@wallace.ece.rice.edu обсуждение исходный текст |
Ответ на | Search and Replace ("Randy D. McCracken" <rdm@srs.fs.usda.gov>) |
Ответы |
Re: Search and Replace
|
Список | pgsql-sql |
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 frommy 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
В списке pgsql-sql по дате отправления: