pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with regexp-query
3+ messages / 3 participants
[nested] [flat]
* Help with regexp-query
@ 2011-07-28 11:20 Johann Spies <jspies@sun.ac.za>
2011-08-03 04:05 ` Re: Help with regexp-query Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 3+ messages in thread
From: Johann Spies @ 2011-07-28 11:20 UTC (permalink / raw)
To: pgsql-sql
I am struggling a bit to do the following type of update in a table.
I want the content of a field updated like this:
Original:
'||||0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
After update:
'|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
in other words: change all multiple adjacent occurences of '|' to only 1.
I have tried the following query but it fails:
select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
akb_articles limit 100
This ends with 'ERROR: invalid regular expression: quantifier operand
invalid'.
I would apreciate some help with this one please.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"If any of you lack wisdom, let him ask of God, that
giveth to all men liberally, and upbraideth not; and
it shall be given him." James 1:5
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with regexp-query
2011-07-28 11:20 Help with regexp-query Johann Spies <jspies@sun.ac.za>
@ 2011-08-03 04:05 ` Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 0 replies; 3+ messages in thread
From: Tim Landscheidt @ 2011-08-03 04:05 UTC (permalink / raw)
To: pgsql-sql
Johann Spies <jspies@sun.ac.za> wrote:
> I am struggling a bit to do the following type of update in a table.
> I want the content of a field updated like this:
> Original:
> '||||0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
> After update:
> '|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
> in other words: change all multiple adjacent occurences of '|' to only 1.
> I have tried the following query but it fails:
> select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
> akb_articles limit 100
> This ends with 'ERROR: invalid regular expression: quantifier operand
> invalid'.
> I would apreciate some help with this one please.
You need to double the backslashes (e. g. "E'\\|{2,}'");
otherwise the parser will "eat" the first backslash and pass
just "|{2,}" as the second argument to regexp_replace().
Tim
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Help with regexp-query
@ 2011-08-02 17:06 Charlie <scorpdaddy@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Charlie @ 2011-08-02 17:06 UTC (permalink / raw)
To: Johann Spies <jspies@sun.ac.za>; pgsql-sql
select id, regexp_replace(category, (E'\\|{2,}'), E'\|', 'g') as category from
akb_articles limit 100
Backslash in regex doubled. Added global modifier to replace all occurrences.
----- Reply message -----
From: "Johann Spies" <jspies@sun.ac.za>
Date: Thu, Jul 28, 2011 8:20 am
Subject: [SQL] Help with regexp-query
To: <pgsql-sql@postgresql.org>
I am struggling a bit to do the following type of update in a table.
I want the content of a field updated like this:
Original:
'||||0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
After update:
'|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
in other words: change all multiple adjacent occurences of '|' to only 1.
I have tried the following query but it fails:
select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
akb_articles limit 100
This ends with 'ERROR: invalid regular expression: quantifier operand
invalid'.
I would apreciate some help with this one please.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"If any of you lack wisdom, let him ask of God, that
giveth to all men liberally, and upbraideth not; and
it shall be given him." James 1:5
--
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] 3+ messages in thread
end of thread, other threads:[~2011-08-03 04:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-28 11:20 Help with regexp-query Johann Spies <jspies@sun.ac.za>
2011-08-03 04:05 ` Tim Landscheidt <tim@tim-landscheidt.de>
2011-08-02 17:06 Re: [SQL] Help with regexp-query Charlie <scorpdaddy@hotmail.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