ORA-12725: unmatched parentheses in regular expression

Lately i am working a lot with regular expressions.
At first the syntax of regular expressions is rather tough to remember, but after a while a whole world of possibilities opens up to the developer.

One of the most useful features of Oracle's implementation of regular expressions is the support for backreference expressions, sorts of variables holding the values of matching subexpressions delimited by round parentheses "(...)".

While backreference expressions can appear in the regular expression pattern of any REGEXP function and also in the replacement parameter of REGEXP_REPLACE, the example given below applies only to the latter case.

Now you might ask: what's so cool about this backreference expression?
Well, backreference expressions allow you to move around bits of text in an easy way.

Example:

I just registered for the upcoming ODTUG Kaleidoscope Apex Training in Daytona Beach, and i came across a screen where different registration fees were proposed.

Imagine that this information must be stored in a database in a different format, with the currency symbol to the right.

We can do this operation with the help of a regular expressions and function REGEXP_REPLACE.

In the frame below a fully functional Yocoya's Regular Expression Workbench is encapsulated, you can follow the instructions given below and interact with the tool, without leaving the blog.



Then, in the input box containing "put the pattern here" cut and paste:

(\$)([[:digit:]]{1,3})

and in the input box containing "put the replacement here", cut and paste:

\2\1

then click APPLY.

The test report should now display the prices with currency postfix notation.

Backreference expressions must be in the range 1-9 and, as far as i know, you can tell which number refers to which expression by counting the left open parentheses "(" starting from the leftmost position in the pattern, so if we take the following slightly modified expression where i added one more pair of parentheses around it:

((\$)([[:digit:]]{1,3}))
12   3

we'd have to change the replacement string to

\3\2

to keep it working like before.


So, what about ORA-12725?

Try to remove the rightmost parenthesis from

((\$)([[:digit:]]{1,3}))

you'll see that the following error is reported:

ORA-12725: unmatched parentheses in regular expression

This error will be raised only when a round parenthesis is missing, a missing square bracket "[" or "]" would return instead:

ORA-12726: unmatched bracket in regular expression

See you at the ODTUG Kaleidoscope 2007!