0

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.
paolo ricardos
  • 65
  • 4

3 Answers3

2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Yes it worked, I just would like to know if is possible to merge the string, if you give a look in my example ID=3 the "Depto" are twice in the same string, the output should be x1,x2,x3,x4 – paolo ricardos Aug 03 '22 at 09:27
  • I don't know of any way to do a regex find all from Oracle, sans resorting to something like a proc or formal script. I didn't see that you have multiple matches within each string. – Tim Biegeleisen Aug 03 '22 at 09:29
1

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

astentx
  • 6,393
  • 2
  • 16
  • 25