Split a string using REGEXP and CONNECT BY

27/04/19 11:53

You have a string of tokens separated by a char, here is how to split it using regexp and connect by to return a table usable in a WHERE IN condition


SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
    FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;



Or using json_table:

select subs from json_table(
replace(json_array('a,b,c,d,e,"'), ',', '","'),
'$[*]' columns (
subs varchar2(4000) path '$'
)
);



----- check if string list conforms to condition "only value of a list"
with data as (
    select 1 as nsq, '18|28' as to_check from dual
    union
    select 2 as nsq, '7|34' as to_check from dual
    union
    select 3 as nsq, '8|10|28' from dual
)

, checker as (
    select d.nsq, splitted.value from data d,
    lateral (
        SELECT REGEXP_SUBSTR(d.to_check, '[^|]+', 1, LEVEL) AS value FROM dual
            CONNECT BY REGEXP_SUBSTR(d.to_check, '[^|]+', 1, LEVEL) IS NOT NULL <
    ) splitted
)
select distinct nsq from checker where value not in (
        SELECT REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL) AS value FROM dual
            CONNECT BY REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL) IS NOT NULL

);

--- returns all possible sum for the members of the list
with checker as (
SELECT level as rn, to_number(REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL)) AS value FROM dual
            CONNECT BY REGEXP_SUBSTR('18|28|38|48', '[^|]+', 1, LEVEL) IS NOT NULL
)
, cte(value, rn, somme) as (
    select value, rn, value as somme from checker
    union all
    select k.value, k.rn, k.value + c.somme from checker k
        join cte c on k.rn > c.rn
)
select distinct somme from cte order by somme ;


--- pattern insensitive to number and kind of separator
SELECT REGEXP_SUBSTR('18 ,28 |38;48+a*58', '[[:digit:]]+', 1, LEVEL) AS value FROM dual
            CONNECT BY REGEXP_SUBSTR('18 ,28 |38;48+a*58', '[[:digit:]]+', 1, LEVEL) IS NOT NULL ;