pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNegative lookbehind assertions in regexs
5+ messages / 4 participants
[nested] [flat]
* Negative lookbehind assertions in regexs
@ 2005-08-29 13:11 Julian Scarfe <julian@avbrief.com>
2005-08-29 16:21 ` Re: Negative lookbehind assertions in regexs Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 5+ messages in thread
From: Julian Scarfe @ 2005-08-29 13:11 UTC (permalink / raw)
To: pgsql-sql
I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex.
In Perl I'd use a negative lookbehind assertion (?<!AB)CD to do the job:
$ cat lb.pl
print "CD: ", 'CD' =~ /(?<!AB)CD/, "\n";
print "XYCD: ", 'XYCD' =~ /(?<!AB)CD/, "\n";
print "ABCD: ", 'ABCD' =~ /(?<!AB)CD/, "\n";
$ perl lb.pl
CD: 1
XYCD: 1
ABCD:
But Postgresql (7.4) doesn't seem to like that:
# select 'ABCD' ~ '(?<!AB)CD';
ERROR: invalid regular expression: quantifier operand invalid
Is there a workaround that allows me to do this as a single regex?
I know I could and together a ~ and !~ like this
# select ('CD' ~ 'CD') and ('CD' !~ 'ABCD');
?column?
----------
t
(1 row)
# select ('ABCD' ~ 'CD') and ('ABCD' !~ 'ABCD');
?column?
----------
f
(1 row)
but changing the SQL would break the existing paradigm.
TIA
Julian
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Negative lookbehind assertions in regexs
2005-08-29 13:11 Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
@ 2005-08-29 16:21 ` Bruno Wolff III <bruno@wolff.to>
2005-09-03 14:59 ` Re: Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2005-08-29 16:21 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
On Mon, Aug 29, 2005 at 14:11:37 +0100,
Julian Scarfe <julian@avbrief.com> wrote:
> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex.
>
> Is there a workaround that allows me to do this as a single regex?
>
> I know I could and together a ~ and !~ like this
>
> # select ('CD' ~ 'CD') and ('CD' !~ 'ABCD');
> ?column?
> ----------
> t
> (1 row)
>
> # select ('ABCD' ~ 'CD') and ('ABCD' !~ 'ABCD');
> ?column?
> ----------
> f
> (1 row)
The above code won't work because there could be both CD and ABCD in the
string. What you want to do is match all of the valid possibilities.
Something like:
(^.?CD)|([^B]CD)|([^A]BCD)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Negative lookbehind assertions in regexs
2005-08-29 13:11 Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
2005-08-29 16:21 ` Re: Negative lookbehind assertions in regexs Bruno Wolff III <bruno@wolff.to>
@ 2005-09-03 14:59 ` Julian Scarfe <julian@avbrief.com>
2005-09-03 15:28 ` Re: Negative lookbehind assertions in regexs Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-09-03 17:18 ` Re: Negative lookbehind assertions in regexs Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Julian Scarfe @ 2005-09-03 14:59 UTC (permalink / raw)
To: pgsql-sql
>> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the
>> regex.
From: "Bruno Wolff III" <bruno@wolff.to>
> Something like:
> (^.?CD)|([^B]CD)|([^A]BCD)
Thanks to Bruno, and to Dawid who replied offline. The above does the job
nicely.
Any plans for a Perl Compatible Regular Expression operator?
http://www.pcre.org/
Or are two regex operators enough?
Julian
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Negative lookbehind assertions in regexs
2005-08-29 13:11 Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
2005-08-29 16:21 ` Re: Negative lookbehind assertions in regexs Bruno Wolff III <bruno@wolff.to>
2005-09-03 14:59 ` Re: Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
@ 2005-09-03 15:28 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2005-09-03 15:28 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
On Sat, Sep 03, 2005 at 03:59:18PM +0100, Julian Scarfe wrote:
> Any plans for a Perl Compatible Regular Expression operator?
> http://www.pcre.org/
Why have Perl-Compatible things when you can have Perl itself? Just use
plperl.
> Or are two regex operators enough?
Only two? We have not only our own code, but also Perl's engine,
Python's, Tcl's, and PHP's, available through the PLs. That's more than
enough, I'd say.
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Negative lookbehind assertions in regexs
2005-08-29 13:11 Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
2005-08-29 16:21 ` Re: Negative lookbehind assertions in regexs Bruno Wolff III <bruno@wolff.to>
2005-09-03 14:59 ` Re: Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
@ 2005-09-03 17:18 ` Dawid Kuroczko <qnex42@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dawid Kuroczko @ 2005-09-03 17:18 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
On 9/3/05, Julian Scarfe <julian@avbrief.com> wrote:
>
> >> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the
> >> regex.
>
> From: "Bruno Wolff III" <bruno@wolff.to>
>
> > Something like:
> > (^.?CD)|([^B]CD)|([^A]BCD)
>
> Thanks to Bruno, and to Dawid who replied offline. The above does the job
> nicely.
I intended to post Cc: to the list, but somehow I didn't (blame it on
computers,
I just assumed Cc is set ;)).
Anyway, when perl_re's are craved for, once could use PLperl for this:
CREATE OR REPLACE FUNCTION perl_re(v text, r text)
RETURNS boolean LANGUAGE plperl STRICT IMMUTABLE AS $$
my ($val, $re) = @_;
return ($val =~ m{$re}) ? 't' : 'f';
$$;
...though it should be noted that queries WHERE perl_re(col, '(?<!AB)CD')
will not use indexes. (unless there are functional indexes on that function,
but then you would need one index for each regex used).
Regards,
Dawid
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-03 17:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-29 13:11 Negative lookbehind assertions in regexs Julian Scarfe <julian@avbrief.com>
2005-08-29 16:21 ` Bruno Wolff III <bruno@wolff.to>
2005-09-03 14:59 ` Julian Scarfe <julian@avbrief.com>
2005-09-03 15:28 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-09-03 17:18 ` Dawid Kuroczko <qnex42@gmail.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