WITH T AS ( SELECT 'A' AS v1, '1;2;3;4;5' AS v2 FROM DUAL UNION ALL SELECT 'B', '1;2;' FROM DUAL UNION ALL SELECT 'C', '1;2;3' FROM DUAL UNION ALL SELECT 'D', '1' FROM DUAL ), TC AS ( SELECT MAX(REGEXP_COUNT(v2, ';') + 1) AS cnt FROM T ) SELECT v1, REGEXP_SUBSTR(v2, '[^;]+', 1, lv) FROM T, (SELECT LEVEL AS lv FROM TC CONNECT BY LEVEL <= cnt) WHERE REGEXP_SUBSTR(v2, '[^;]+', 1, lv) IS NOT NULL ORDER BY v1, lv