How to migrate data from the old table to the new table in oracle by removing the comma from the column values

Question

I have to insert the data into the New_Table from the Old_Table
There are approx 10k record into the old table
My Old_Table data looks like this

ID          Structure
001         APC , AMC , Self Service, Change of Billing Address, EPC, 
002         APC , Self Service, EPC, OBA, 
003         EPC, 
004         AMC
005         Self Service

The New_Table should look like this

        ID          Structure
        001         APC
        001         AMC
        001         Self Service
        001         Change of Billing Address
        001         EPC
        001         (empty/space)
        002         APC
        002         Self Service
        002         EPC
        002         OBA
        002         (empty/space)
        003         EPC
        003         (empty/space)
        004         AMC
        005         Self Service

What will be the easiest way to resolve the issue and migrate the data.
Thanks

possible duplicate of [Oracle SQL: the insert query with REGEXP\_SUBSTR expression is very long ( split string )](http://stackoverflow.com/questions/18787116/oracle-sql-the-insert-query-with-regexp-substr-expression-is-very-long-split) — Nick Krasnov, Mar 13 '14 at 20:21
Also you can get some clues from [this answer](http://stackoverflow.com/a/13190045/2899629). — Yaroslav Shabalin, Mar 13 '14 at 20:34

score 1 · Answer 1 · answered Mar 14 '14 at 01:54

Try the following sql to split the values. The table ALL_TABLES is only used as a driver. You can use every table which has as many rows as you has single values between the commas.

insert into a values (1, 'A,B,C')
/
insert into a values (2, 'E,F')
/
insert into a values (3, 'ASD,BSF,BERT,BROT')
/
select * from a
/
select id, s, v, b, substr(s, v+1, b-v-1)
from (
select id, s, instr(s, ',', 1, n) v, instr(s, ',', 1, n+1) b
from (
select a.id id, ','||a.s||',' s, c.n
from a a, (select rownum n from all_tables) c
))
where v > 0 and b > 0
/

Here is a link to sql fiddle to play around http://www.sqlfiddle.com/#!4/f6ab9/2/4

score 0 · Accepted Answer · answered Mar 17 '14 at 14:21

I found the answer to the question. The below query did work for me.

insert into New_Table (ID, Structure)
(select ID, trim(Structure) 
from (
WITH TT AS
 (SELECT ID, Structure FROM Old_Table)
 SELECT ID, substr(str,
                   instr(str, ',', 1, LEVEL) + 1,
                   instr(str, ',', 1, LEVEL + 1) -
                   instr(str, ',', 1, LEVEL) - 1) Structure
     FROM   (SELECT ID, rownum AS r,
                    ','|| Structure||',' AS STR
               FROM   TT )
     CONNECT BY PRIOR r = r
         AND    instr(str, ',', 1, LEVEL + 1) > 0
         AND    PRIOR dbms_random.STRING('p', 10) IS NOT NULL)
         where trim(Structure) is not null);

How to migrate data from the old table to the new table in oracle by removing the comma from the column values

2 Answers2