WITH src as (
select --+cardinality(e 1)
'(((a=1 or b=1) and (y=3 or z=1)) and c=1 and x=5 or Attribute04 NOT IN (1, ''GED'') and y>7)'
expr from dual
), idxs as (
select i
from (select rownum i from dual connect by rownum < 4000) a, src
where i<=LENGTH(EXPR) and (substr(EXPR,i,1)='('
or substr(EXPR,i,1)=' ' or substr(EXPR,i,1)=')' )
--where i<=LENGTH(EXPR) and (substr(EXPR,i,1)='(' or lower(substr(EXPR,i,3))='and'
--or lower(substr(EXPR,i,2))='or' or substr(EXPR,i,1)=')' )
), lbri as (
select i
from idxs, src
where substr(EXPR,i,1)='('
and lower(substr(EXPR,i-3,1))<>'in' -- added code
), rbri as (
select i
from idxs, src
where substr(EXPR,i,1)=')'
and lower(substr(EXPR,i-3,1))<>'in' -- added code
), wtsp as (
select i
from idxs, src
where (substr(EXPR,i,1)=' ')
), begi as (
select i+1 x from wtsp
union all
select i x from lbri
union all
select i+1 x from lbri
), endi as ( -- [x,y)
select i y from wtsp
union all
select i+1 y from rbri
union all
select i y from rbri
), ranges as ( -- [x,y)
select x, y
from begi a, endi b
where x < y
), wffs1 as (
select x, y from ranges r
--
-- bracket balance:
where (select count(1) from lbri where i between x and y-1)
= (select count(1) from rbri where i between x and y-1)
--
-- eliminate '...)...(...'
-- equals when no occurrencies
and (select coalesce(min(i),0) from lbri where i between x and y-1)
<= (select coalesce(min(i),0) from rbri where i between x and y-1)
), wffs as ( -- reduce outer brackets: (..<wffs1>..)
select x+1 x, y-1 y from wffs1 w
where (x in (select i from lbri)
and y-1 in (select i from rbri)
and not exists (select i from rbri where i between x+1 and y-2
and i < all(select i from lbri where lbri.i between x+1 and y-2))
)
union all
select x, y from wffs1 w
where not (x in (select i from lbri)
and y-1 in (select i from rbri)
and not exists (select i from rbri where i between x+1 and y-2
and i < all(select i from lbri where lbri.i between x+1 and y-2))
)
--) select x, y, substr(EXPR, x, y-x) || '!'
--from wffs r, src s where s.num=r.num --and (substr(EXPR, x, y-x) = 'a=1 or b=1' or substr(EXPR, x, y-x) = '(a=1 or b=1)' )
), andi as (
select x i
from wffs a, src s
where lower(substr(EXPR, x, 3))='and'
), ori as (
select x i
from wffs a, src s
where lower(substr(EXPR, x, 2))='or'
), or_wffs as (
select x, y, i from ori a, wffs w where x <= i and i <= y
and (select count(1) from lbri l where l.i between x and a.i-1)
= (select count(1) from rbri r where r.i between x and a.i-1)
), and_wffs as (
select x, y, i from andi a, wffs w where x <= i and i <= y
and (select count(1) from lbri l where l.i between x and a.i-1)
= (select count(1) from rbri r where r.i between x and a.i-1)
and (x,y) not in (select x,y from or_wffs ww)
), other_wffs as (
select x, y from wffs w
minus
select x, y from and_wffs w
minus
select x, y from or_wffs w
), max_or_wffs as (
select distinct x, y from or_wffs w
where not exists (select 1 from or_wffs ww
where ww.x<w.x and w.y<=ww.y and w.i=ww.i)
and not exists (select 1 from or_wffs ww
where ww.x<=w.x and w.y<ww.y and w.i=ww.i)
), max_and_wffs as (
select distinct x, y from and_wffs w
where not exists (select 1 from and_wffs ww
where ww.x<w.x and w.y<=ww.y and w.i=ww.i)
and not exists (select 1 from and_wffs ww
where ww.x<=w.x and w.y<ww.y and w.i=ww.i)
), max_other_wffs as (
select distinct x, y from other_wffs w
where not exists (select 1 from other_wffs ww where ww.x<w.x and w.y<=ww.y)
and not exists (select 1 from other_wffs ww where ww.x<=w.x and w.y<ww.y)
), predicates as (
select 'OR' typ, x, y, substr(EXPR, x, y-x) expr
from max_or_wffs r, src s
union all
select 'AND', x, y, substr(EXPR, x, y-x)
from max_and_wffs r, src s
union all
select '', x, y, substr(EXPR, x, y-x)
from max_other_wffs r, src s
) select * from predicates
;