[SQL] Search and Replace
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 GroupV_/_ USDA/FS - Southern Research Station E-Mail: [EMAIL PROTECTED] 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
Re: [SQL] Search and Replace
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: > > > > 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. [EMAIL PROTECTED] > 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
Re: [SQL] Search and Replace
On Thu, 9 Jan 2003, Ross J. Reedstrom wrote: > On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote: > > > > any anyone explain whats wrong with the replace based solution to this problem > > which i posted earlier? > > > > did i misunderstood anything? > > Probably just overkill - I'm sure it would work, but, based on how the > question was asked, I guessed that the original questioner was looking > for a quick, onetime fix sort of thing, and wasn't real comfortable with > SQL, let alone adding contrib extension products to the installation. > > Ross > > Hi Rajesh, I don't know why but the example you gave me did not work. Here is what happened when I tried: == pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; ERROR: Function 'replace(unknown, unknown, text)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts == Obviously I am a newbie at PostgreSQL (I have also joined the Novice mailing list to learn more) so I can not tell you why your example did not work, perhaps someone else with more experience can add some information. Best, --rdm ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
