Row-to-Column

9i -- Multiple rows With temp (Select '1970 1600302,6001600303, 100' text from dual Union Select '2014 1600302,7001600303' text from dual ) Select substr (text, instr (text, ',', 1, RN) + 1, instr (text, ',', 1, rn + 1)-instr (text, ',', 1, RN)-1) text from ( Select ',' | t1.text | ', 'text, t2.rn from (Select text, length (text)-length (replace (text, ',', '') + 1 rn from temp) T1, (Select rownum rn from all_objects where rownum <= (select max (length (text)-length (replace (text, ',', '') + 1) rn from temp) T2 Where t1.rn> = t2.rn order by text, Rn ) Text 6001600301 6001600302 6001600303 6001600304 7001600301 7001600302 7001600303 -- Single row With temp (Select '2014 1600302,6001600303, 100' text from dual) Select substr (text, instr (text, ',', 1, RN) + 1, instr (text, ',', 1, rn + 1)-instr (text, ',', 1, RN)-1) text from ( Select ',' | t1.text | ', 'text, t2.rn from temp T1, (Select rownum rn from all_objects where rownum <= (select length (text)-length (replace (text, ',') + 1 from temp) T2 ) Text 6001600301 6001600302 6001600303 6001600304 10 GB -- Multiple rows With temp (Select '1970 1600302,6001600303, 100' text from dual Union Select '2014 1600302,7001600303' text from dual ) Select regexp_substr (text, '[0-9] +', 1, RN) text from ( Select t1.text, t2.rn from (Select text, length (text)-length (replace (text, ',', '') + 1 rn from temp) T1, (Select level RN from dual connect by rownum <= (select max (length (text)-length (replace (text, ',') + 1) rn from temp) T2 Where t1.rn> = t2.rn order by text, Rn ) Text 6001600301 6001600302 6001600303 6001600304 7001600301 7001600302 7001600303 -- Single row With temp (Select '2014 1600302,6001600303, 100' text from dual) Select regexp_substr (text, '[0-9] +', 1, RN) text from temp T1, (Select level RN from dual connect by rownum <= (select length (text)-length (replace (text, ',') + 1 from temp) T2 Text 6001600301 6001600302 6001600303 6001600304 Source: http://www.itpub.net/thread-1145824-1-1.html>

From Weizhi note (wiz)

Row-to-Column