Hi!
Assume you have a text like 'one (a1) (b1)'. The text in parenthesis is a remark. The text may have multiple remarks. I'd like to extract all the remarks and create separate rows for each.
Sample data:
with tbl as (
select 1 as nr, 'one (a1) (a2)' as text from dual union all
select 2 as nr, 'two (b1)' as text from dual union all
select 3 as nr, 'three (a3) (b3)' as text from dual
)
select * from tbl;
I am looking for a query that would return:
NR
| TEXT | idx | REMARK |
|---|
| 1 | one (a1) (a2) | 1 | (a1) |
| 1 | one (a1) (a2) | 2 | (a2) |
| 2 | two (b1) | 1 | (b1) |
| 3 | three (a3) (b3) | 1 | (a3) |
| 4 | three (a3) (b3) | 2 | (b3) |
Searching the net, I found a pointer to hierarchical queries... - I tried:
with tbl as (
select 1 as nr, 'one (a1) (a2)' as text from dual union all
select 2 as nr, 'two (b1)' as text from dual union all
select 3 as nr, 'three (a3) (b3)' as text from dual
)
select
nr,
text,
level as idx,
regexp_substr(text, '\([^)]+\)', 1, level) AS remark
from
tbl a
connect by regexp_substr(text, '\([^)]+\)', 1, level) is not null -- and nr = prior nr
;
However, this returns
NR TEXT IDX REMARK
1 one (a1) (a2) 1 (a1)
1 one (a1) (a2) 2 (a2)
3 three (a3) (b3) 2 (b3)
2 two (b1) 1 (b1)
1 one (a1) (a2) 2 (a2)
3 three (a3) (b3) 2 (b3)
3 three (a3) (b3) 1 (a3)
1 one (a1) (a2) 2 (a2)
3 three (a3) (b3) 2 (b3)
I.e. the first remark in each text is at level 1 and is shown as a predecessor of any 2nd remark. Clearly, I want the hierarchy within a single line, only. So, I tried to add the condition "and nr = prior nr" in the connect by (as indicated in the code above), but this results in a error message (ORA-01436: CONNECT BY loop in user data)...