From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: Randy D. McCracken <rdm@srs.fs.usda.gov>
To: pgsql-sql@postgresql.org
Subject: Re: Search and Replace
Date: Wed, 8 Jan 2003 21:07:00 +0530
Message-ID: <200301082107.00427.mallah@trade-india.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
References: <Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, rdm@srs.fs.usda.gov
Subject: Re: Search and Replace
In-Reply-To: <200301082107.00427.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox