WITH TT(A, B) AS (
SELECT 5, '1|2' FROM DUAL UNION ALL
SELECT 3, '1|2' FROM DUAL UNION ALL
SELECT 10, '3|5' FROM DUAL
)
SELECT A
, REGEXP_SUBSTR(B,'[^|]+',1,LV) B
FROM TT
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100)
WHERE REGEXP_SUBSTR(B,'[^|]+',1,LV) IS NOT NULL
ORDER BY 1,2
;
by 마농
[2015.10.15 09:15:20]
SELECT a
, lv
, SUBSTRING_INDEX(SUBSTRING_INDEX(b, '|', lv), '|', -1) b
FROM (SELECT 5 a, '1|2' b
UNION ALL SELECT 3, '1|2'
UNION ALL SELECT 10, '3|5'
) a
CROSS JOIN
(SELECT 1 lv UNION ALL SELECT 2) b
;
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.