Break comma-separed values in a table into rows

In this blog, I am going to talk  about an interesting way to split a comma-separated list into individual rows.

To achieve this consider a table called junk_tab with a column called names .

SQL> select names from junk_tab;

NAMES
--------------------------------------------------------------------------------
gautham,siddhu,surya
frank,shriram,vimal

SQL> desc junk_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAMES                                              VARCHAR2(4000 CHAR)

What i intend to do is to split the individual names (delimited by comma) into separate rows.

Therefore at the end of the exercise we will have a select query returning 6 rows as
gautham
siddhu
..
..
..
vimal

6 rows selected

There are many ways to achieve this and infact there are several ways to achieve the same end result.

I am going to teach you step by step about what I wanted to do .

Step 1
======

For every row in the table I want to get the number of comma-separated values.

Here to get this the logic is subtract the length of the string after the comma's have ben stripped off from the original
length of the string .The result will give you the count of the commas.Adding 1 to it will give you the number of
comma-separated variables.

SQL> select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab;

NAMES
----------------------------------------------------------------------------------------------------
       XXX
----------
gautham,siddhu,surya
         3

frank,shriram,vimal
         3

Step 2
=======
The below sql is used for generating a sequence of number starting from 1 till what we specify.

SQL> select level num from dual connect by level <=5;

       NUM
----------
         1
         2
         3
         4
         5
        
In the below query, note the "with tmp as" I have used to include the above query as a temporary view.
        
Now do the following

SQL> with tmp as ( select level num from dual connect by level <= 100 )
  2  select test123.names,tmp.num
  3  from
  4  (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
  5  where tmp.num <=test123.xxx
  6  order by test123.names,tmp.num
  7  /

NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
frank,shriram,vimal
         1

frank,shriram,vimal
         2

frank,shriram,vimal
         3


NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
gautham,siddhu,surya
         1

gautham,siddhu,surya
         2

gautham,siddhu,surya
         3
        
Step 3
+++++++++

This is easy as we just have to extract the 1st string from row 1, 2nd string from row 2 etc.



SQL> with tmp as ( select level num from dual connect by level <= 100 )
  2  select
  3  substr(test123.names ,
  4    decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1)),
  5    decode(instr(test123.names,',',1,tmp.num),0,length(test123.names)+1,instr(test123.names,',',1
,tmp.num)) - decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1))
  6    )
  7  from
  8  (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
  9  where tmp.num <=test123.xxx
 10  order by test123.names,tmp.num
 11  /

SUBSTR(TEST123.NAMES,DECODE(TMP.NUM,1,1,(INSTR(TEST123.NAMES,',',1,(TMP.NUM-1))+1)),DECODE(INSTR(TES
----------------------------------------------------------------------------------------------------
frank
shriram
vimal
gautham
siddhu
surya

6 rows selected.

SQL>


6 rows selected.