Обсуждение: how to do a find and replace

Hello-

I have a column of data with the wrong prefix for many items.

The wrong entries are entered ' /0/v.myimage.jpg'
While the correct ones are ' /0/myimage.jpg'


I need to remove all the 'v.' characters from this column.


I'm able to do a

SELECT * FROM myTable
WHERE location = '%/0/v.%'


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.

thank you
Dawn

am  17.11.2005, um 14:51:05 -0800 mailte Dawn Buie folgendes:
> Hello-
> 
> I have a column of data with the wrong prefix for many items.

If you have 8.1? If yes:

> 
> The wrong entries are entered ' /0/v.myimage.jpg'
> While the correct ones are ' /0/myimage.jpg'
> 
> 
> I need to remove all the 'v.' characters from this column.a


test=# select * from foo;       loc
------------------- /0/v.myimage.jpg
(1 row)

test=# update foo set loc = regexp_replace(loc, '/v\.', '/');
UPDATE 1
test=# select * from foo;      loc
----------------- /0/myimage.jpg
(1 row)



If no:

a) Upgrade now!
b) write a funcion with plperl for the regexp_replace


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

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