PostgreSQL regexp with case-insensitive parts

Question

Using PostgreSQL v.8.2.14, I'm trying to build a regexp with several branches, some of which are case-insensitive, the others not.

Consider the following perl one-liner:

% echo 'foo Foo bar Bar' | perl -pe 's/(foo|(?i:bar))/_\1/g' _foo Foo _bar _Bar

I thought I would get there with:

select regexp_replace('foo Foo bar Bar','(foo|((?i)bar)',E'_\\1','g');

But I get: ERROR: invalid regular expression: quantifier operand invalid. Note that the regex_flavor is advanced, and BTW when I put the (?i) at the very beginning of the regexp, then there is no error:

select regexp_replace('foo Foo bar Bar','(?i)(foo|bar)',E'_\\1','g'); _foo _Foo _bar _Bar

Any help gladly appreciated.

mu is too short · Accepted Answer · 2017-01-26 04:55:43Z

The (?i) (and related options) are only valid at the beginning of the expression. From the fine manual:

An ARE may begin with embedded options: a sequence (?xyz) (where xyz is one or more alphabetic characters) [...]

Emphasis mine. The (?xyz) options are like the trailing /.../xyz options in the regexes of other languages. Also note that the 9.0 manual uses the same language so you can't just upgrade your way around this.

Looks like you need two regexp_replace calls:

> select regexp_replace(regexp_replace('foo Foo bar Bar', 'foo', E'_\\&', 'g'), 'bar', E'_\\&', 'ig'); regexp_replace -------------------- _foo Foo _bar _Bar

Or do the case-insensitive matching the hard way (i.e. character classes):

> select regexp_replace('foo Foo bar Bar', '(foo|[Bb][Aa][Rr])', E'_\\1', 'g'); regexp_replace -------------------- _foo Foo _bar _Bar

... (I wasn't finished with my comment...) well thanks for the answer, but that's what I was afraid of. The problem with a multiple-regexp approach is that it cuts any backtracking the regexp engine would normally do. For example, looking at /(Foobar|(?i:bar))/, I want it to match only once at the beginning of 'Foobar', and once at position 3 of 'fooBar'. Since I'm building those regexps programmatically anyway, I could do the "case-insensitive the hard way" approach. Thanks.

Collectives™ on Stack Overflow

PostgreSQL regexp with case-insensitive parts

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related