Re: Search and Replace - Mailing list pgsql-sql
From | Rajesh Kumar Mallah. |
---|---|
Subject | Re: Search and Replace |
Date | |
Msg-id | 200301082107.00427.mallah@trade-india.com Whole thread Raw |
In response to | Search and Replace ("Randy D. McCracken" <rdm@srs.fs.usda.gov>) |
List | pgsql-sql |
REPLACE is the right function for you avaliable as an addon. install it in postgresql installation using the source at: http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23 if you face problem please lemme know once this function is installed you could update like: -------------- UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; -------------- regds mallah. On Wednesday 08 January 2003 07:32 pm, 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. > > I am responsible for managing a database containing over 6,000 records of > US Forest Service Research publications > (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple > search and replace in one of the columns. In these records we have a > field for URLs of the location the research publications and I need to > change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It > seems like this "search and replace" would be a simple thing to do with an > UPDATE command but I am having great difficulty making this work. > > The table definition I am trying to update is: url. At first I thought > about using a simple UPDATE command like this: > > UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = > 'www.srs.fs.fed.us'; > > Of course that would work fine but "www.srs.fs.fed.us" is only part of a > complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My > problem (among other things!) is that I don't know how to pass along > wildcards so that I do not change the other parts of the complete URL. I > have tried substituting "like" for "=" and trying to use the wildcard of > "%" but to no avail. I am really just guessing here. > > Any help would be greatly appreciated! > > Best, > > --rdm > > > ======================================= > Randy D. McCracken (0> > Web Guy //\ > Communications Group V_/_ > > USDA/FS - Southern Research Station > > E-Mail: rdm@srs.fs.usda.gov > Voice: (828) 259-0518 > Fax: (828) 257-4840 > Web: http://www.srs.fs.fed.us/ > ======================================= > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- 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.