SQL For converting delimitted string to individual rows

ORACLE SQL For converting delimitted string row into individual rows

SELECT
STRTEXT
,SUBSTR
(STRTEXT,
DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1),
(CASE WHEN ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1) <0 THEN
(LENGTH(STRTEXT) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
ELSE
((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
END)
) AS SUBSTR
,DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1) AS STARTNUM
, (CASE WHEN ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1) <0 THEN
(LENGTH(STRTEXT) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
ELSE ((INSTR(STRTEXT,DELIMTR,1,ROWNUM)-1) - DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)+1)+1)
END) AS ENDNUM
FROM ALL_VIEWS
,
(
SELECT
'aWW,bDD,cDD,dFF,eAA,fCC' AS STRTEXT
, ',' AS DELIMTR
FROM PSCLOCK
)SUBQ
WHERE DECODE(ROWNUM-1,0,1,INSTR(STRTEXT,DELIMTR,1,ROWNUM-1)) <> 0