-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE maindata(mainid NUMBER, liste VARCHAR2(1000), PRIMARY KEY(mainid));
INSERT INTO maindata VALUES (1, '1,2,4,8');
INSERT INTO maindata VALUES (2, '1,3,5,7');
CREATE TABLE supdata(supid NUMBER, txt VARCHAR2(20), PRIMARY KEY(supid));
INSERT INTO supdata VALUES(1, 'Number 1');
INSERT INTO supdata VALUES(2, 'Number 2');
INSERT INTO supdata VALUES(3, 'Number 3');
INSERT INTO supdata VALUES(4, 'Number 4');
INSERT INTO supdata VALUES(5, 'Number 5');
INSERT INTO supdata VALUES(6, 'Number 6');
SELECT * FROM maindata;
SELECT * FROM supdata;
CREATE TYPE number_table_type AS TABLE OF NUMBER;
/
CREATE FUNCTION get_split_liste(txt IN VARCHAR2, del IN VARCHAR2 := ',') RETURN number_table_type PIPELINED AS
tmp VARCHAR2(1000) := txt;
ix NUMBER;
BEGIN
    LOOP
        ix := INSTR(tmp, del);
        IF ix > 0 THEN
            PIPE ROW(SUBSTR(tmp, 1, ix-1));
            tmp := SUBSTR(tmp, ix + LENGTH(del));
        ELSE
            PIPE ROW(tmp);
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END;
/
SELECT * FROM TABLE(get_split_liste('1,2,3')); 
SELECT * FROM TABLE(get_split_liste('4*5*6', '*'));
CREATE FUNCTION get_all_split_liste(del IN VARCHAR2 := ',') RETURN number_table_type PIPELINED AS
tmp VARCHAR2(1000);
ix NUMBER;
BEGIN
    FOR cur IN (SELECT liste FROM maindata)
    LOOP
        tmp := cur.liste;
        LOOP
            ix := INSTR(tmp, del);
            IF ix > 0 THEN
                PIPE ROW(SUBSTR(tmp, 1, ix-1));
                tmp := SUBSTR(tmp, ix + LENGTH(del));
            ELSE
                PIPE ROW(tmp);
                EXIT;
            END IF;
        END LOOP;
    END LOOP;
    RETURN;
END;
/
SELECT * FROM TABLE(get_all_split_liste());
SELECT * FROM TABLE(get_all_split_liste()) MINUS SELECT supid FROM supdata;
DROP FUNCTION get_all_split_liste;
DROP FUNCTION get_split_liste;
DROP TYPE number_table_type;
DROP TABLE supdata;
DROP TABLE maindata;
