paolo ricardos
paolo ricardos

Reputation: 65

Oracle SQL - Query to select string between curly Brackets

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.

Upvotes: 0

Views: 803

Answers (3)

astentx
astentx

Reputation: 6751

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

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.

db<>fiddle including a version replacing commas with colons.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You probably would need to escape the metacharacters in your regex. Consider this version:

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

screen capture from demo link below

Demo

Upvotes: 1

Related Questions