From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: "Randy D. McCracken" <rdm@srs.fs.usda.gov>,
	pgsql-sql@postgresql.org
Subject: Re: Search and Replace
Date: Wed, 8 Jan 2003 21:07:00 +0530
User-Agent: KMail/1.5
References: <Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
In-Reply-To: <Pine.LNX.4.44.0301080901150.28293-100000@www.srs.fs.usda.gov>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200301082107.00427.mallah@trade-india.com>


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.