regexp_replace

am Mon, dem 23.07.2007, um 7:50:35 -0700 mailte polen.t2006@freenet.de folgendes:

Hi all,

I would like to change a sub-string in a text-field by using

UPDATE tablename SET
fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string)
WHERE (fieldname like '%old_sub_string%')

In priniciple, it works. However, only one occurence of old_sub_string
is replaced and further not. Which syntax has to be used to replace
all old_sub_strings by new_sub_string in a text-field at once? I have
seen something like '\&' in the docs and tried it, but I failed.

Any Idea? Thanks a lot!

No problem, add a 'g' as extra parameter to your regexp_replace() -
function. See:

test=> select regexp_replace('xxaxxxxaxxxa','a','A');
regexp_replace
----------------
xxAxxxxaxxxa
(1 row)

test=*> select regexp_replace('xxaxxxxaxxxa','a','A','g');
regexp_replace
----------------
xxAxxxxAxxxA
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Mon, Jul 23, 2007 at 07:50:35AM -0700, polen.t2006@freenet.de wrote:

I would like to change a sub-string in a text-field by using

UPDATE tablename SET
fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string)
WHERE (fieldname like '%old_sub_string%')

In priniciple, it works. However, only one occurence of old_sub_string
is replaced and further not. Which syntax has to be used to replace
all old_sub_strings by new_sub_string in a text-field at once?

regexp_replace(fieldname, old_sub_string, new_sub_string, 'g')

For more information search for regexp_replace in the Pattern
Matching section of the Functions and Operators chapter of the
documentation.

http://www.postgresql.org/docs/8.2/interactive/functions-matching.html

--
Michael Fuhr

On Jul 23, 2007, at 9:50 AM, polen.t2006@freenet.de wrote:

fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string)

Add a 'g' flag:

fieldname=regexp_replace(fieldname,old_sub_string,new_sub_string, 'g')

From the end of a paragraph in 9.7.3 just before 9.7.3.1:

The flags parameter is an optional text string containing zero or
more single-letter flags that change the
function’s behavior. Flag i specifies case-insensitive matching,
while flag g specifies replacement of each
matching substring rather than only the first one.

Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems