pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to do a find and replace
8+ messages / 5 participants
[nested] [flat]
* how to do a find and replace
@ 2005-11-17 22:51 Dawn Buie <dawn@squeegeemedia.com>
0 siblings, 2 replies; 8+ messages in thread
From: Dawn Buie @ 2005-11-17 22:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* how to do a find and replace
@ 2005-11-17 23:47 Dawn Buie <dawn@squeegeemedia.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dawn Buie @ 2005-11-17 23:47 UTC (permalink / raw)
To: pgsql-sql
Hello-
I'm using postgres 7.4
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-17 23:55 Bricklen Anderson <BAnderson@PresiNET.com>
parent: Dawn Buie <dawn@squeegeemedia.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bricklen Anderson @ 2005-11-17 23:55 UTC (permalink / raw)
To: Dawn Buie <dawn@squeegeemedia.com>; +Cc: pgsql-sql
Dawn Buie wrote:
> Hello-
> I'm using postgres 7.4
>
> 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
How about update myTable set location=replace(location,'v.','');
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-18 00:08 Dawn Buie <dawn@squeegeemedia.com>
parent: Bricklen Anderson <BAnderson@PresiNET.com>
0 siblings, 2 replies; 8+ messages in thread
From: Dawn Buie @ 2005-11-18 00:08 UTC (permalink / raw)
To: Bricklen Anderson <BAnderson@PresiNET.com>; +Cc: pgsql-sql
I used:
update media_instance set location=replace(location,'v.','')
where location like '%/0/v.%'
and that did work-
thank you very much.
it seems to me that the replace function is the same as translate()- no?
On 17-Nov-05, at 3:55 PM, Bricklen Anderson wrote:
> Dawn Buie wrote:
>> Hello-
>> I'm using postgres 7.4
>>
>> 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
>
> How about update myTable set location=replace(location,'v.','');
>
> --
> _______________________________
>
> This e-mail may be privileged and/or confidential, and the sender does
> not waive any related rights and obligations. Any distribution, use or
> copying of this e-mail or the information it contains by other than an
> intended recipient is unauthorized. If you received this e-mail in
> error, please advise me (by return e-mail or otherwise) immediately.
> _______________________________
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-18 00:12 Bricklen Anderson <BAnderson@PresiNET.com>
parent: Dawn Buie <dawn@squeegeemedia.com>
1 sibling, 0 replies; 8+ messages in thread
From: Bricklen Anderson @ 2005-11-18 00:12 UTC (permalink / raw)
To: Dawn Buie <dawn@squeegeemedia.com>; +Cc: pgsql-sql
Dawn Buie wrote:
> I used:
>
> update media_instance set location=replace(location,'v.','')
> where location like '%/0/v.%'
>
> and that did work-
>
> thank you very much.
>
> it seems to me that the replace function is the same as translate()- no?
>
Right, I forgot your WHERE clause.
Some more details on those functions can be found here:
http://www.postgresql.org/docs/8.1/interactive/functions-string.html#FUNCTIONS-STRING-OTHER
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-18 01:10 Hélder M. Vieira <hmv@mail.telepac.pt>
parent: Dawn Buie <dawn@squeegeemedia.com>
1 sibling, 0 replies; 8+ messages in thread
From: Hélder M. Vieira @ 2005-11-18 01:10 UTC (permalink / raw)
To: pgsql-sql
> update media_instance set location=replace(location,'v.','')
> where location like '%/0/v.%'
For more complex search/replace operations, 8.1 provides an interesting
function, named 'regexp_replace'.
It probably goes unnoticed because although there is a small reference in
the manual in section 'E.1.3.6. Data Type and Function Changes'
(http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1)
and a description in section '9.7.3 POSIX Regular expressions'
(http://www.postgresql.org/docs/8.1/interactive/functions-matching.html), it
isn't mentioned in section '9.4. String Functions and Operators'
(http://www.postgresql.org/docs/8.1/interactive/functions-string.html).
Hélder M. Vieira
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-18 06:15 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Dawn Buie <dawn@squeegeemedia.com>
1 sibling, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2005-11-18 06:15 UTC (permalink / raw)
To: pgsql-sql
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 ===
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to do a find and replace
@ 2005-11-18 15:19 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Dawn Buie <dawn@squeegeemedia.com>
1 sibling, 0 replies; 8+ messages in thread
From: Andrew Sullivan @ 2005-11-18 15:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-11-18 15:19 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-17 22:51 how to do a find and replace Dawn Buie <dawn@squeegeemedia.com>
2005-11-18 06:15 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-18 15:19 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-11-17 23:47 how to do a find and replace Dawn Buie <dawn@squeegeemedia.com>
2005-11-17 23:55 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-11-18 00:08 ` Dawn Buie <dawn@squeegeemedia.com>
2005-11-18 00:12 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-11-18 01:10 ` Hélder M. Vieira <hmv@mail.telepac.pt>
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