WITH TEMP AS
(
SELECT 1 INT_ID,'ni' NAME,'12,13,14,a,b,~' REGION_ID FROM DUAL
UNION
SELECT 2 INT_ID,'wo' NAME,'13,14,F' REGION_ID FROM DUAL
UNION
SELECT 3 INT_ID,'ta' NAME,'11' REGION_ID FROM DUAL
)
SELECT T1.INT_ID,T1.NAME,REGEXP_SUBSTR(T1.REGION_ID,'[0-9a-zA-Z~]+',1,T2.LEV) REGION_ID FROM
(SELECT TEMP.*,LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1 RN FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL
CONNECT BY LEVEL <= (SELECT SUM(LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1) RN FROM TEMP)) T2
WHERE T2.LEV <= T1.RN ORDER BY INT_ID,LEV
(
SELECT 1 INT_ID,'ni' NAME,'12,13,14,a,b,~' REGION_ID FROM DUAL
UNION
SELECT 2 INT_ID,'wo' NAME,'13,14,F' REGION_ID FROM DUAL
UNION
SELECT 3 INT_ID,'ta' NAME,'11' REGION_ID FROM DUAL
)
SELECT T1.INT_ID,T1.NAME,REGEXP_SUBSTR(T1.REGION_ID,'[0-9a-zA-Z~]+',1,T2.LEV) REGION_ID FROM
(SELECT TEMP.*,LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1 RN FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL
CONNECT BY LEVEL <= (SELECT SUM(LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1) RN FROM TEMP)) T2
WHERE T2.LEV <= T1.RN ORDER BY INT_ID,LEV