I'm looking for an query to select string between curly Brackets , I can utilize in Oracle SQL or PL/SQL.

Here is an example TABLE1 to work with:

ID	LIST
1	Depto:={x1,x2} Code:={house}
2	Depto:={y1,x1} Code:={house,garden}
3	Depto:={x1,x2} Code:={house} Depto:={x3,x4}
4	Code:={house,garden} Depto:={y1,x1}

Result expected:

ID	LIST	DEPTO	CODE
1	Depto:={x1,x2} Code:={house}	x1:x2	house
2	Depto:={y1,x1} Code:={house,garden}	y1:x1	house:garden
3	Depto:={x1,x2} Code:={house} Depto:={x3,x4}	x1:x2:x3:x4	house
4	Code:={house,garden} Depto:={y1,x1}	y1:x1	house:garden

I tried this query:

SELECT id, list, regexp_substr(list, '\Depto:={([^}] )\}', 1,1,NULL,1) AS Depto, regexp_substr(list, '\Code:={([^}] )\}', 1,1,NULL,1) AS Code FROM table1

but with this query I extrated only the first string.

Thanks.

CodePudding user response：

You could use recursive subquery factoring (or a hierarchical query) to extract all of the depto/code elements from each list:

with rcte (id, list, lvl, depto, code) as (
  select id, list,
    1,
    regexp_substr(list, 'Depto:={(.*?)}', 1, 1, null, 1),
    regexp_substr(list, 'Code:={(.*?)}', 1, 1, null, 1)
  from table1
  union all
  select id, list,
    lvl   1,
    regexp_substr(list, 'Depto:={(.*?)}', 1, lvl   1, null, 1),
    regexp_substr(list, 'Code:={(.*?)}', 1, lvl   1, null, 1)
  from rcte
  where regexp_substr(list, 'Depto:={(.*?)}', 1, lvl   1, null, 1) is not null
  or regexp_substr(list, 'Code:={(.*?)}', 1, lvl   1, null, 1) is not null
)
select * from rcte

ID	LIST	LVL	DEPTO	CODE
1	Depto:={x1,x2} Code:={house}	1	x1,x2	house
2	Depto:={y1,x1} Code:={house,garden}	1	y1,x1	house,garden
3	Depto:={x1,x2} Code:={house} Depto:={x3,x4}	1	x1,x2	house
4	Code:={house,garden} Depto:={y1,x1}	1	y1,x1	house,garden
3	Depto:={x1,x2} Code:={house} Depto:={x3,x4}	2	x3,x4	null

and then use listagg to aggregate them together:

with rcte (id, list, lvl, depto, code) as (
  select id, list,
    1,
    regexp_substr(list, 'Depto:={(.*?)}', 1, 1, null, 1),
    regexp_substr(list, 'Code:={(.*?)}', 1, 1, null, 1)
  from table1
  union all
  select id, list,
    lvl   1,
    regexp_substr(list, 'Depto:={(.*?)}', 1, lvl   1, null, 1),
    regexp_substr(list, 'Code:={(.*?)}', 1, lvl   1, null, 1)
  from rcte
  where regexp_substr(list, 'Depto:={(.*?)}', 1, lvl   1, null, 1) is not null
  or regexp_substr(list, 'Code:={(.*?)}', 1, lvl   1, null, 1) is not null
)
select id, list,
  listagg(depto, ',') within group (order by lvl) as depto,
  listagg(code, ',') within group (order by lvl) as code
from rcte
group by id, list

ID	LIST	DEPTO	CODE
1	Depto:={x1,x2} Code:={house}	x1,x2	house
2	Depto:={y1,x1} Code:={house,garden}	y1,x1	house,garden
3	Depto:={x1,x2} Code:={house} Depto:={x3,x4}	x1,x2,x3,x4	house
4	Code:={house,garden} Depto:={y1,x1}	y1,x1	house,garden

If you want colons instead of commas then you can just replace those, either before or after aggregating.

Demo

CodePudding user response：

The structure of input data looks quite simple, so you may also try to convert it to JSON and parse that JSON. It will not require recursion to split parts into different items.

with table1(id, list) as (
  select rownum, column_value
  from table(sys.odcivarchar2list(
    'Depto:={x1,x2} Code:={house}'
    , 'Depto:={y1,x1} Code:={house,garden}'
    , 'Depto:={x1,x2} Code:={house} Depto:={x3,x4}'
    , 'Code:={house,garden} Depto:={y1,x1}'
  ))
)
, a as (
  select
    id
    , list
    /*Replace each pair <Code>:=<Value> to JSON-like structure*/
    , '['
      || regexp_replace(list || ' ', '(\w ):=\{([^}] )\} ', '{"\1":"\2"},')
      || ']' as res
  from table1
)
select
  a.id
  , translate(
      replace(json_query(res, '$[*].."Depto"' with array wrapper), ',', ':')
      , ' "[]'
      , ' '
  ) as depto
  , trim(both '"' from json_query(res, '$[*].."Code"')) as code
from a 

ID	DEPTO	CODE
1	x1:x2	house
2	y1:x1	house,garden
3	x1:x2:x3:x4	house
4	y1:x1	house,garden

db<>fiddle here