SQL oracle query to select part of string between two delimiters

Question

I am using REGEXP_SUBSTR to select part of a string. The full string is either of the form text (more_text (other_text)) or text (more_text (other_text)) (the difference here is the extra space before the beginning second bracket). The part of the string I want to collect in each case is 'more_text'.

I currently have the command

REGEXP_SUBSTR(string, '\((.*)\(', 1, 1, NULL, 1)

which works for the first format of string but doesn't return anything for the second. I'm unsure why the double space means it doesn't match. How can I change this to make it work in both cases?

EDIT: I realised that actually it isn't a double space it's a fullwidth parenthesis, which has the encoding %EF%BC%88. Is there a way of matching that character?

Answer 1

Does it have to be regular expression? If not, how about substr + instr between 1st and 2nd open bracket?

SQL> with test (col) as
  2    (select 'text (more_text (other_text))' from dual union all
  3     select 'text (more_text  (other_text))' from dual
  4    )
  5  select col,
  6    trim(substr(col, instr(col, '(') + 1,
  7                     instr(col, '(', 1, 2) - instr(col, '(') - 1
  8               )
  9        ) result
 10  from test;

COL                            RESULT
------------------------------ ------------------------------
text (more_text (other_text))  more_text
text (more_text  (other_text)) more_text

SQL>

Answer 2

I believe you just need to make your group non-greedy and match one or spaces following before the next paren like this:

with tbl(id, str) as (
  select 1, 'text (more_text (other_text))' from dual union all
  select 2, 'text (more_text  (other_text))' from dual
)
select id, 
       regexp_substr(str, '\((.*?)\s+\(', 1, 1, null, 1) extract,
       length(regexp_substr(str, '\((.*?)\s+\(', 1, 1, null, 1)) length
from tbl
order by id;


        ID EXTRACT                            LENGTH
---------- ------------------------------ ----------
         1 more_text                               9
         2 more_text                               9

2 rows selected.