pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow 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>
2013-03-26 13:13 ` Re: how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:22 ` Re: how can I replace all instances of a pattern Steve Crawford <scrawford@pinpointresearch.com>
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: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 ` Re: how can I replace all instances of a pattern ktm@rice.edu <ktm@rice.edu>
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:08 how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:13 ` Re: how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
@ 2013-03-26 13:18 ` ktm@rice.edu <ktm@rice.edu>
2013-03-26 13:31 ` Re: how can I replace all instances of a pattern 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:08 how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:13 ` Re: how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
2013-03-26 13:18 ` Re: how can I replace all instances of a pattern ktm@rice.edu <ktm@rice.edu>
@ 2013-03-26 13:31 ` James Sharrett <jsharrett@tidemark.net>
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
* Re: how can I replace all instances of a pattern
2013-03-26 13:08 how can I replace all instances of a pattern James Sharrett <jsharrett@tidemark.net>
@ 2013-03-26 13:22 ` Steve Crawford <scrawford@pinpointresearch.com>
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
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