Splitting a comma delimited string

Splitting a comma delimited string

SQL> with test as

  2  (

  3  select 1 id, 'joey,anthony,marvin' str from dual union all

  4  select 5 id, 'tony,glenn' str from dual union all

  5  select 8 id, 'john' str from dual

  6  )

  7  select id

  8       , str

  9       , regexp_substr (str, '[^,]+', 1, rn) split

 10    from test

 11    cross

 12    join (select rownum rn

 13            from (select max (length (regexp_replace (str, '[^,]+'))) + 1 mx

 14                    from test

 15                 )

 16         connect by level <= mx

 17         )

 18   where regexp_substr (str, '[^,]+', 1, rn) is not null

 19   order by id

 20  ;

        ID STR                 SPLIT

---------- ------------------- -------------------

         1 joey,anthony,marvin joey

         1 joey,anthony,marvin marvin

         1 joey,anthony,marvin anthony

         5 tony,glenn          tony

         5 tony,glenn          glenn

         8 john                john

Source: http://nuijten.blogspot.in/2011/08/splitting-comma-delimited-string-regexp.html