Selecting values between brackets in sql oracle-CodePudding

here are a lot of examples e.g. using regular expressions how to retrieve texts between brackets, but I haven't found one that can do it with multi-level brackets.

E.g. I have the text:

{AB}{C}{DEF{XX}}

I would like to get results like:

AB
C
DEF{XX}
XX

or the second option, which would also be fine for me, is not returning texts that contain other brackets, i.e:

AB
C
XX

Here a standard query using a regular expression that works on single level brackets:

SELECT REGEXP_SUBSTR('{AB}{C}{DEF}', '{(.*?)}', 1, level, NULL, 1) AS str
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('{AB}{C}{DEF}', '{(.*?)}');

Result:

AB
C
DEF

this regular expression will not work correctly for the text {AB}{C}{DEF{XX}}, where the result will be:

AB
C
DEF{XX

CodePudding user response：

I'm not good at regular expressions so this can probably be shortened; meanwhile, see if it helps.

Sample data:

SQL> with test (col) as
  2    (select '{AB}{C}{DEF{XX}}' from dual union all
  3     select '{XY}'             from dual),

Split bracketed values into rows; this piece of code also takes care about multiple rows in sample table (e.g. 2 rows in my example; there was only one in your example. If that's always the case, you can remove lines #9 - 11 and use level instead of column_value:

  4  temp as
  5    (select col,
  6            column_value cv,
  7            regexp_substr(col, '([^}] )', 1, column_value) val
  8     from test cross join
  9       table(cast(multiset(select level from dual
 10                           connect by level <= regexp_count(col, '{')
 11                          ) as sys.odcinumberlist))
 12    )

Finally, remove leading curly bracket and add closing one if necessary:

 13  select col,
 14    ltrim(case when regexp_count(val, '{') > 1 then val || '}' else val end, '{') result
 15  from temp
 16  where val is not null
 17  order by col, cv;

COL              RESULT
---------------- --------------------
{AB}{C}{DEF{XX}} AB
{AB}{C}{DEF{XX}} C
{AB}{C}{DEF{XX}} DEF{XX}
{XY}             XY

SQL>

CodePudding user response：

This example returns only the contents of the innermost sets of curly braces. [\{\}] allows any number of any character except { & }

SELECT REGEXP_SUBSTR('{AB}{C}{DEF}{HI{JKL}}', '{([^\{\}] )}', 1, level, NULL, 1) AS str
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('{AB}{C}{DEF}{HI{JKL}}', '{(.*?)}');

| STR |
| :-- |
| AB  |
| C   |
| DEF |
| JKL |

db<>fiddle here