X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6F0D9DBAB8 for ; Fri, 18 Nov 2005 11:19:32 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36870-02 for ; Fri, 18 Nov 2005 15:19:35 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp102.rog.mail.re2.yahoo.com (smtp102.rog.mail.re2.yahoo.com [206.190.36.80]) by svr1.postgresql.org (Postfix) with SMTP id A03E6DBAC3 for ; Fri, 18 Nov 2005 11:19:29 -0400 (AST) Received: (qmail 16513 invoked from network); 18 Nov 2005 15:19:30 -0000 Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@209.222.54.227 with login) by smtp102.rog.mail.re2.yahoo.com with SMTP; 18 Nov 2005 15:19:30 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 34AF840E6; Fri, 18 Nov 2005 10:19:29 -0500 (EST) Date: Fri, 18 Nov 2005 10:19:29 -0500 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: how to do a find and replace Message-ID: <20051118151929.GB28967@phlogiston.dyndns.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/147 X-Sequence-Number: 23398 On Thu, Nov 17, 2005 at 02:51:05PM -0800, Dawn Buie wrote: > I'm just confused about how I should write code to update the selected > items to remove the 'v.' > > Would I use substring? An example would be much appreciated. You need a combination of overlay and location. The following will work if you always have _only_ 'v.' in there in the one place you want it. If it is too early in the string, this _won't_ work: andrewtest=# SELECT version(); version ------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) (1 ligne) andrewtest=# SELECT * from mytable ; location ----------------------------- /0/v.myimage.jpg /0/v.myotherimage.jpg /0/v.myvthotherimage.jpg /0/v.myvthotherv.image.jpg (4 lignes) Note that I've fiddled with the initial spacing here, in case that hasn't been totally consistent either. This is for illustration. andrewtest=# select overlay(location placing '' from (position('v.' in location)) for 2) from mytable; overlay --------------------------- /0/myimage.jpg /0/myotherimage.jpg /0/myvthotherimage.jpg /0/myvthotherv.image.jpg (4 lignes) Note here that the _second_ 'v.' in the last entry doesn't get pulled out. These functions work on the first hit, so this is as expected. But if you have something like '/v.0/v.myimage.jpeg' you'll not lose the 'v.' you want, I expect. A -- Andrew Sullivan | ajs@crankycanuck.ca The whole tendency of modern prose is away from concreteness. --George Orwell