Re: regexp_replace

am Mon, dem 23.07.2007, um 7:50:35 -0700 mailte polen(dot)t2006(at)freenet(dot)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