Hi Experts,
I am running Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
I have a question about using regular expressions in combination with connect by to get all occurrences of an expression in the same field. For instance, if you would like to find all text enclosed in {} in a varchar2:
select regexp_substr('{a} {b} {c}', '\{[a-z][a-z0-9_]*\}', 1, level, 'im') matches
from dual
connect by level <= regexp_count('{a} {b} {c}', '\{[a-z][a-z0-9_]*\}', 1, 'im')
This returns:
MATCHES
{a}
{b}
{c}
This works fine for a single row. I am having problems extrapolating this to a table that has multiple rows:
with example as
( select 1 id, '{a} {b} {c}' text from dual union all
select 2 id, '{c} {d} {e}' text from dual union all
select 3 id, '{x} {y} {z}' text from dual union all
select 4 id, '' text from dual )
select id, regexp_substr(text, '\{[a-z][a-z0-9_]*\}', 1, level, 'im') matches
from example
connect by level <= regexp_count(text, '\{[a-z][a-z0-9_]*\}', 1, 'im')
But something seems to be missing.
I would like to have the id and the matches. In this case, the expected output is
ID MATCHES
1 {a}
1 {b}
1 {c}
2 {c}
2 {d}
2 {e}
3 {x}
3 {y}
3 {z}
Can you please point me in the right direction?
Thanks!
Rop