CODEWITHSUNDEEP
sqloracle-database
Amzi
Amzi

Reputation: 399

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

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

Upvotes: 0

Views: 93

Answers (2)

Amzi
Amzi

Reputation: 399

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);

Upvotes: 0

Christian13467
Christian13467

Reputation: 5614

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

Upvotes: 1

Related Questions