how can I replace all instances of a pattern

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

how can I replace all instances of a pattern
5+ messages / 3 participants
[nested] [flat]

* how can I replace all instances of a pattern
@ 2013-03-26 13:08  James Sharrett <jsharrett@tidemark.net>
  0 siblings, 2 replies; 5+ messages in thread

From: James Sharrett @ 2013-03-26 13:08 UTC (permalink / raw)
  To: pgsql-sql

I'm trying remove all instances of non-alphanumeric or underscore characters
from a query result for further use.  This is part of a function I'm writing
that is in plpgsql

Examples:

  Original value
    'My text1'
    'My text 2'
    'My-text-3'
    'My_text4'
    'My!text5'

   Desired
    'Mytext1'
    'Mytext2'
    'Mytext3'
    'My_text4'  (no change)
    'Mytext5'

The field containing the text is column_name.  I tried the following:

  Select regexp_replace(column_name,'\W','') from mytable

This deals with the correct characters but only does the first instance of
the character so the output is:

    'My text1'
    'Mytext 2'  (wrong)
    'Mytext-3'  (wrong)
    'My_text4'
    'My!text5'

I managed to get the desired output by writing the text into a variable
through a loop and then just keep looping on the variable until all the
characters are removed:

sql_qry:= 'select column_name from mytable';

for sql_record in execute sql_qry loop
curr_record := sql_record.column_name;

        while length(substring(curr_record from '\W'))>0 loop
           curr_record := regexp_replace(curr_record, '\W','');
        end loop;

. rest of the code

This works but it seems like a lot of work to do something this simple but I
cannot find any function that will replace all instances of a string AND can
base it on a regular expression pattern.  Is there a better way to do this
in 9.1?

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how can I replace all instances of a pattern
@ 2013-03-26 13:13  James Sharrett <jsharrett@tidemark.net>
  parent: James Sharrett <jsharrett@tidemark.net>
  1 sibling, 1 reply; 5+ messages in thread

From: James Sharrett @ 2013-03-26 13:13 UTC (permalink / raw)
  To: pgsql-sql

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'

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how can I replace all instances of a pattern
@ 2013-03-26 13:18  ktm@rice.edu <ktm@rice.edu>
  parent: James Sharrett <jsharrett@tidemark.net>
  0 siblings, 1 reply; 5+ messages in thread

From: ktm@rice.edu @ 2013-03-26 13:18 UTC (permalink / raw)
  To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how can I replace all instances of a pattern
@ 2013-03-26 13:22  Steve Crawford <scrawford@pinpointresearch.com>
  parent: James Sharrett <jsharrett@tidemark.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Steve Crawford @ 2013-03-26 13:22 UTC (permalink / raw)
  To: pgsql-sql

On 03/26/2013 06:08 AM, James Sharrett wrote:
> I'm trying remove all instances of non-alphanumeric or underscore 
> characters from a query result for further use.  This is part of a 
> function I'm writing that is in plpgsql
>
> Examples:
>
>   Original value
>     'My text1'
>     'My text 2'
>     'My-text-3'
>     'My_text4'
>     'My!text5'
>
>    Desired
>     'Mytext1'
>     'Mytext2'
>     'Mytext3'
>     'My_text4'  (no change)
>     'Mytext5'
>
>
> The field containing the text is column_name.  I tried the following:
>
>   Select regexp_replace(column_name,'\W','') from mytable
>
> This deals with the correct characters but only does the first 
> instance of the character so the output is:
>
>     'My text1'
>     'Mytext 2'  (wrong)
>     'Mytext-3'  (wrong)
>     'My_text4'
>     'My!text5'
>
> I managed to get the desired output by writing the text into a 
> variable through a loop and then just keep looping on the variable 
> until all the characters are removed:
>
> sql_qry:= 'select column_name from mytable';
>
> for sql_record in execute sql_qry loop
> curr_record := sql_record.column_name;
>
> while length(substring(curr_record from '\W'))>0 loop
> curr_record := regexp_replace(curr_record, '\W','');
> end loop;
>
> .... rest of the code
>
> This works but it seems like a lot of work to do something this simple 
> but I cannot find any function that will replace all instances of a 
> string AND can base it on a regular expression pattern.  Is there a 
> better way to do this in 9.1?

You were on the right track with regexp_replace but you need to add a 
global flag:
regexp_replace(column_name,'\W','','g')

See examples under 
http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Cheers,
Steve

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how can I replace all instances of a pattern
@ 2013-03-26 13:31  James Sharrett <jsharrett@tidemark.net>
  parent: ktm@rice.edu <ktm@rice.edu>
  0 siblings, 0 replies; 5+ messages in thread

From: James Sharrett @ 2013-03-26 13:31 UTC (permalink / raw)
  To: pgsql-sql

Thanks Ken!  I missed that option going through the documentation.

>




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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

end of thread, other threads:[~2013-03-26 13:31 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-26 13:08 how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:13 ` James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:18   ` ktm@rice.edu <ktm@rice.edu>
2013-03-26 13:31     ` James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:22 ` Steve Crawford <scrawford@pinpointresearch.com>

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