WITH t AS
(SELECT 9 ID, 'a,b,d' NAME
FROM DUAL
UNION ALL
SELECT 10, 'c,e'
FROM DUAL)
SELECT ID,
SUBSTR (NAME,
INSTR (NAME, ',', 1, LEVEL) + 1,
INSTR (NAME, ',', 1, LEVEL + 1)
- INSTR (NAME, ',', 1, LEVEL)
- 1
) NAME
FROM (SELECT ID, ',' || NAME || ',' NAME
FROM t)
CONNECT BY PRIOR ID = ID
AND LEVEL <= LENGTH (NAME) - LENGTH (REPLACE (NAME, ',')) - 1
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
|
