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: Thu, 9 Jan 2003 11:00:32 +0530
User-Agent: KMail/1.5
Cc: pgsql-novice@postgresql.org
References: <Pine.LNX.4.44.0301081506150.7559-100000@www.srs.fs.usda.gov>
In-Reply-To: <Pine.LNX.4.44.0301081506150.7559-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: <200301091100.32667.mallah@trade-india.com>


any anyone explain whats wrong with the replace based solution to this problem
which i posted earlier?

did i misunderstood anything?


regds
mallah.

On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote:
> 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'||s
>ubstr(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:
> > <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'||sub
> >str(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.                                 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
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.