Splitting String using Oracle SQL 9i

Run following query to split values separated by comma(,)


Ex:1:-

SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1
                     ) )
         AS GS
   FROM ( SELECT ','||:in_string||',' AS txt  FROM dual )
 CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1

Example
-------
If value of in_string is entered as AMAR,ALAM,AMARALAM

Output is
9GS
---
AMAR
ALAM
AMARALAM


Ex:2:-


select regexp_substr('Amar.Alam.Amaralam','[^ .]+',1,level) GS from dual
connect by regexp_substr('Amar.Alam.Amaralam','[^.]+',1,level) is not null
/

Outout:

=====

GS

Amar

Alam

AmarAlam

Ur's
AmarAlam