REGEXP_SUBSTR: Unmatched Parentheses in regular expression error

Question

Background: I have 3 columns: Item, Description, Description2. An item can be replaced with another item number, and is indicated in the description or description2 columns. Description and Description2 contains descriptions for the item, and whenever there is a replacement item for that item the description or description2 can contain some common phrasing to indicate to use the new item number. Example: REPLACE W 12345 OLD 54321. So I have a 4th column I want to extract that 12345 number into.

The code used is below. Essentially I found that if there is a new item number to use, it will always be setup like this: common phrase followed by a space and then followed by the item number. Another example would be: SEE 12345.

I had to use a concat with a space after because the description fields are limited to 30 characters, and sometimes the description will use the whole 30 characters with the item replacement number at the very end. My code looks to stop at the white space after the number. Whenever I added the concat piece, it gives the following error:

ORA-12725: unmatched parentheses in regular expression 12725. 00000 - "unmatched parentheses in regular expression" *Cause: The regular expression did not have balanced parentheses. *Action: Ensure the parentheses are correctly balanced.

I've check several times and I cannot find any unmatched parentheses. Any idea why I'm getting this error? I commented out one line at a time, and it errors out on REPLACE W . Although, when I ran with:

REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)') it seems to run fine. I need to have the case when piece with it though.

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(ITEM )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(ITEM )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(ITEM )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(ITEM )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(NEW )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(NEW )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(NEW )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(NEW )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPL W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPL W )(\d+)(\s)'),'(\d+)')

when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPL W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPL W )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W (\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE RX )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE RX )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE RX )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE RX )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SUB TO )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SUB TO )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SUB TO )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SUB TO )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)')

--when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)')*/

I don't have access to oracle sql so I cannot get this out of the text editor. Appreciate the thoughts.

score 0 · Answer 1 · answered Feb 02 '21 at 00:56

Your SQL looks fine to me

SQL> create table t ( description varchar2(10), description2 varchar2(10));

Table created.

SQL>
SQL> select
  2  case
  3  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)')
  4  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE TEMP )(\d+)(\s)'),'(\d+)')
  5  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(ITEM )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(ITEM )(\d+)(\s)'),'(\d+)')
  6  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(ITEM )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(ITEM )(\d+)(\s)'),'(\d+)')
  7  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(NEW )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(NEW )(\d+)(\s)'),'(\d+)')
  8  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(NEW )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(NEW )(\d+)(\s)'),'(\d+)')
  9  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPL W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPL W )(\d+)(\s)'),'(\d+)')
 10  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPL W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPL W )(\d+)(\s)'),'(\d+)')
 11  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)')
 12  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W (\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W )(\d+)(\s)'),'(\d+)')
 13  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)')
 14  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(REPLACE W/)(\d+)(\s)'),'(\d+)')
 15  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE )(\d+)(\s)'),'(\d+)')
 16  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE )(\d+)(\s)'),'(\d+)')
 17  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE RX )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE RX )(\d+)(\s)'),'(\d+)')
 18  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE RX )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE RX )(\d+)(\s)'),'(\d+)')
 19  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)')
 20  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SEE SUB )(\d+)(\s)'),'(\d+)')
 21  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SUB TO )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(SUB TO )(\d+)(\s)'),'(\d+)')
 22  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SUB TO )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(SUB TO )(\d+)(\s)'),'(\d+)')
 23  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)')
 24  when REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)') is not null then REGEXP_SUBSTR(REGEXP_SUBSTR(i.description2||' ', '(TEMP HOLD )(\d+)(\s)'),'(\d+)')
 25  end
 26  from t i;

no rows selected

Appreciate the response. Well, the code shouldn't have worked if you tried running it.. When you re-posted the code, I saw immediately the missing parentheses. I guess I had just been starting at the code for too long and had to step away for a day. Thanks! — kyleuser1, Feb 03 '21 at 12:35

REGEXP_SUBSTR: Unmatched Parentheses in regular expression error

1 Answers1