Date: Tue, 26 Mar 2013 08:18:52 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: James Sharrett <jsharrett@tidemark.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: how can I replace all instances of a pattern
Message-ID: <20130326131852.GF32580@aart.rice.edu>
References: <CD771612.8A7C%jsharrett@tidemark.net>
	<CD7716CB.8A7D%jsharrett@tidemark.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CD7716CB.8A7D%jsharrett@tidemark.net>
User-Agent: Mutt/1.5.20 (2009-12-10)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On Tue, Mar 26, 2013 at 09:13:39AM -0400, James Sharrett wrote:
> Sorry, caught a typo.  Mytext1 is correctly replaced because only one
> instance of the character (space) is in the string.
> 
> This deals with the correct characters but only does the first instance of
> the character so the output is:
> 
>     'Mytext1'
>     'Mytext 2'  (wrong)
>     'Mytext-3'  (wrong)
>     'My_text4'
>     'My!text5'
> 

Hi James,

Try adding the g flag to the regex (for global). From the documentation:

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
                                   fooXarYXazY

Regards,
Ken


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql