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.

Answer 1

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