sql - Oracle complex string replacement -

sql - Oracle complex string replacement -

i've got next table

mytable type | id | name | formula "simple" | 1 | "count" | "<1>" "simple" | 2 | "distinct" | "<2>" "simple" | 3 | "mycol" | "<3>" "complex" | 4 | null | "<1>(<2> <3>)"

now read table , add together additional column replaces formula string. id 4 need: "count(distinct mycol)" thought how can that?

in oracle 11 may looks this:

select type, id, name, formula, value mytable left bring together ( select id_complex, listagg(decode(pos, 2, name, part)) within grouping (order occ, pos) value ( select id_complex, occ, pos, regexp_replace(pair, '^(.*?)(<.*?>)$', '\'||pos) part ( select id id_complex, occ, regexp_substr(formula||'<>', '.*?<.*?>', 1, occ) pair ( select level occ dual connect level <= (select max(length(formula)) mytable) ), mytable type = 'complex' ), (select level pos dual connect level <= 2) ) left bring together mytable on part = formula , type = 'simple' grouping id_complex ) on id = id_complex order id

sql fiddle

sql oracle plsql oracle10g