How to insert the comma separated value in table

Question

I have a table like that:

columnId1 => "word1,word2,word3"
columnId3 => "word4,word5,word6"

I want to create a table like that:

columnId1  word1
columnId1  word2
columnId1  word3
columnId2  word4
columnId2  word5
columnId2  word6

How can I do it in PLSQL?

Answer 1

create table testtable(col1 varchar2(50) , col2 varchar2(50));

insert into testtable (col1, col2)
with commadata as(
select 'word1,word2,word3' columnid1, 'word4,word5,word6' columnid2
  from dual
  )
select       regexp_substr( columnid1 
                          , '[^,]+'
                          ,1
                          ,level)    as parsed_value
                          ,
regexp_substr( columnid2 
                          , '[^,]+'
                          ,1
                          ,level)    as parsed_value                          

  from commadata
connect by level  <= REGEXP_COUNT( columnid1 , '[,]' )+1    
;  
select * from testtable;  

COL1                                               COL2                                               
-------------------------------------------------- -------------------------------------------------- 
word1                                              word4                                              
word2                                              word5                                              
word3                                              word6   

this is assuming you are using 10g and have access to Regular Expressions. this should give you enough to go on to create your statements, do note it is brittle and can break if columnId1 and columnId2 have different amounts of commas and such.

Answer 2

Quite some time ago I wrote a custom function for this sort of need. I was working on an application that routinely would want to store comma separated values in the database. I had to create a function to select it out as if it were a table and a function for the reverse so that the application was happy. I can't seem to find the function I ultimately came up with but I wrote it off a response from Tom to a question I asked here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2189860818012#28667254837962