From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: how to do a find and replace
Date: Fri, 18 Nov 2005 10:19:29 -0500
Message-ID: <20051118151929.GB28967@phlogiston.dyndns.org> (raw)
In-Reply-To: <c051e0b633ba9015ecd98f6c038783e0@squeegeemedia.com>
References: <c051e0b633ba9015ecd98f6c038783e0@squeegeemedia.com>
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
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: ajs@crankycanuck.ca
Subject: Re: how to do a find and replace
In-Reply-To: <20051118151929.GB28967@phlogiston.dyndns.org>
* 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