By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (value) AS
SELECT '{AB}{C}{DEF{XX}}' FROM DUAL UNION ALL
SELECT '{A{B{C{D{E}D}C}B}A}' FROM DUAL;
2 rows affected
WITH find_tokens (rid, value, pos, type, depth) AS (
SELECT ROWID,
value,
REGEXP_INSTR(value, '[{}]'),
DECODE(REGEXP_SUBSTR(value, '[{}]'), '{', 1, '}', 0 ),
DECODE(REGEXP_SUBSTR(value, '[{}]'), '{', 1, '}', 0 )
FROM table_name
UNION ALL
SELECT rid,
value,
REGEXP_INSTR(value, '[{}]', pos + 1),
DECODE(REGEXP_SUBSTR(value, '[{}]', pos + 1), '{', 1, '}', 0 ),
depth + DECODE(REGEXP_SUBSTR(value, '[{}]', pos + 1), '{', 1, '}', -1 )
FROM find_tokens
WHERE pos > 0
)
SEARCH DEPTH FIRST BY rid SET row_order
SELECT SUBSTR(value, spos + 1, epos - spos - 1) AS match
FROM find_tokens
MATCH_RECOGNIZE(
PARTITION BY rid
ORDER BY pos
MEASURES
FIRST(value) AS value,
FIRST(pos) AS spos,
LAST(pos) AS epos
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN ( first_row any_row*? last_row )
DEFINE
first_row AS type = 1,
last_row AS type = 0 AND depth = FIRST(depth) - 1
)
| MATCH |
|---|
| AB |
| C |
| DEF{XX} |
| XX |
| A{B{C{D{E}D}C}B}A |
| B{C{D{E}D}C}B |
| C{D{E}D}C |
| D{E}D |
| E |