ORA-12727: Invalid Back Reference In Regular Expression

If you’ve run across ORA-12727: invalid back reference in regular expression, it’s because you’ve referenced a group (a group is an expression enclosed in parenthesis) either that doesn’t exist, or doesn’t exist yet. There are two common reasons for this mistake.

Referencing A Group Before It Exists

One way to run into this error is to reference a group that either doesn’t exist, or doesn’t yet exist. Here’s an example of referencing a group that doesn’t exist:

select regexp_replace('Moose', '^(M)(o)\3+(se)$', '\1\2\3')
from dual;

Here, we create group 1 (M). Then we create group 2 (o). Next, we reference group 3; but the problem is group 3 does not yet exist at this point of reading the regular expression. You would only be able to reference a group after it’s been created, like this:

select regexp_replace('Moose', '^(M)(o)\2+(se)$', '\1\2\3')
from dual;

The above example is perfectly fine. This is because we’re referencing the second group (o) after it’s been created.

Mis-Typing The Regular Expression

Another reason you might experience this issue is just from a simple typo. Take for example the following query:

select case when regexp_like(stooge, '^[[:alnum:]]+ ([[:alnum:]]+)$')
             then regexp_replace(stooge, '^[[:alnum:]]+ ([[:alnum:]]+)$', '\1')
            when regexp_like(stooge, '^Mrs?\. [[:alnum:]]+ ([[:alnum:]]+)$')
             then regexp_replace(stooge, '^Mrs?\. [[:alnum:]]+ ([[:alnum:]]+)$', '\1')
            when regexp_like(stooge, '^[[:alnum:]]+ [[:alnum:]]\. [[:alnum:]]+$')
             then regexp_replace('^[[:alnum:]]+ [[:alnum:]]\. ([[:alnum:]]+)$', '\1')
       end last_name
from (select 'Moe H. Howard' stooge from dual
      union all
      select 'Mr. Larry Fine' from dual
      union all
      select 'Curley Howard' from dual)

It fails with ORA-12727: invalid back reference in regular expression, simply because the final regexp_replace() function didn’t include the STOOGE column. So the query should read like this:

select case when regexp_like(stooge, '^[[:alnum:]]+ ([[:alnum:]]+)$')
             then regexp_replace(stooge, '^[[:alnum:]]+ ([[:alnum:]]+)$', '\1')
            when regexp_like(stooge, '^Mrs?\. [[:alnum:]]+ ([[:alnum:]]+)$')
             then regexp_replace(stooge, '^Mrs?\. [[:alnum:]]+ ([[:alnum:]]+)$', '\1')
            when regexp_like(stooge, '^[[:alnum:]]+ [[:alnum:]]\. [[:alnum:]]+$')
             then regexp_replace(stooge, '^[[:alnum:]]+ [[:alnum:]]\. ([[:alnum:]]+)$', '\1')
       end last_name
from (select 'Moe H. Howard' stooge from dual
      union all
      select 'Mr. Larry Fine' from dual
      union all
      select 'Curley Howard' from dual )

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets