Getting text from multiple brackets

Question

For Oracle SQL

i have the below tag format: {ABCDE,12345}{47,CE}

SELECT 
    [column a] 
    [column b] 
    [column c] 
FROM 
    [table name] 
WHERE 
    [*content of column d*] IN {ABCDE,12345} 
    [*content of column e*] IN {47,CE}

So i need my query to look at what's in the tag and select all rows that have the same contents if: - column D = the first set of brackets - column E = the content in the second set

Also, there are plenty of existing questions on tokenizing strings [such as this one](https://stackoverflow.com/q/14328621/146325). You need to explain what makes your problem different enough to require another one. — APC, Jan 07 '20 at 08:01
@APC i actually realize how badly i explained my needs, so i'll start again. {ABCDE,12345}{47,CE} is a tag. my query looks like this: SELECT [column a] [column b] [column c] FROM [table name] WHERE [content of column d] IN {ABCDE,12345} [content of column e] IN {47,CE} I need a dinamic formula though, since the data between brackets {} is subject to change, therefore i need the query to match it depending on the contents between brackets. Hope this makes any sense. — Andrei Calin, Jan 07 '20 at 08:13
Can you show some actual data? How do you "have" the tag data? Is it coming from a column? — Gary_W, Jan 07 '20 at 14:28

score 0 · Answer 1 · answered Jan 07 '20 at 07:58

One option might be this (see comments within code):

SQL> with test (col) as
  2    (select '{ABCDE,12345}{47,CE}' from dual),
  3  temp as
  4    -- remove leading and trailing brackets
  5    -- replace "middle" brackets with semi-colon (a new separator)
  6    (select replace(substr(col, 2, length(col) - 2), '}{', ';') val
  7     from test
  8    )
  9  select regexp_substr(val, '[^;]+', 1, level) result
 10  from temp
 11  connect by level <= regexp_count(val, ';') + 1;

RESULT
--------------------------------------------------------------------
ABCDE,12345
47,CE

SQL>

Ah, yes - separate columns: then, substr might help:

SQL> with test (col) as
  2    (select '{ABCDE,12345}{47,CE}' from dual)
  3  select substr(col, 2, instr(col, '}{') - 2) val1,
  4         substr(substr(col, instr(col, '}{') + 2),
  5                1,
  6                length(substr(col, instr(col, '}{') + 2)) - 1
  7               ) val2
  8  from test;

VAL1        VAL2
----------- -----
ABCDE,12345 47,CE

SQL>

Getting text from multiple brackets

1 Answers1