CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
BaseBallBatBoy
BaseBallBatBoy

Reputation: 685

Oracle complex string replacement

I've got the following 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 I would like to read this table and add an additional column which replaces the formula string. For id 4 I would need: "COUNT(DISTINCT mycol)"
Any idea how I can do that?

Upvotes: 1

Views: 278

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

In Oracle 11 it may looks like this: 

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

SQL Fiddle

Upvotes: 1

Related Questions