Split one column value into multiple columns - stored procedure

Question

I have a table called product which contains a column called 'Description'. The values of the description column will be like

'NAME:ITEM1;COST:20;QUANTITY:23;'
'NAME:ITEM2;COST:20;QUANTITY:23;'
'NAME:ITEM4;COST:24;QUANTITY:24;'
'NAME:ITEM6;COST:26;QUANTITY:25;'
'NAME:ITEM3;COST:27;QUANTITY:27;'

Now I have another table called PRODUCT_DETAILS which has three columns NAME, COST, QUANTITY.

I have to split the values by ':',';' and extract the values alone into the PRODUCT_DETAILS table.

I should use stored procedure to do this. Please help me to sort this out as I had written only simple queries and stored procedures in SQL

Are you familiar on Oracle's `REGEXP` functionality? If yes - use it, otherwise learn it. You may want to use [this tutorial](http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm). — PM 77-1, Sep 19 '13 at 18:08
possible duplicate of [Splitting comma separated string in a PL/SQL stored proc](http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc) — davidethell, Sep 19 '13 at 18:09
Thanks everyone. Can I have any sample. I have tried many solutions including the suggestions above. But couldn't get a solution — user2796551, Sep 19 '13 at 18:15

score 1 · Answer 1 · answered Sep 19 '13 at 18:49

You don't need a stored procedure for this. As you know the format of description you can easily select the values and insert them into product_details:


insert into product_details
(name, cost, quantity)
select 
  substr(description, instr(description, ':', 1, 1) + 1, instr(description, ';', 1, 1) - instr(description, ':', 1, 1) - 1) as name,
  to_number(substr(description, instr(description, ':', 1, 2) + 1, instr(description, ';', 1, 2) - instr(description, ':', 1, 2) - 1)) as cost,
  to_number(substr(description, instr(description, ':', 1, 3) + 1, instr(description, ';', 1, 3) - instr(description, ':', 1, 3) - 1)) as quantity
from product;

Of course you can also write a procedure containing the statement:


create or replace procedure product_to_product_details is
begin
  insert into product_details
  (name, cost, quantity)
  select 
    substr(description, instr(description, ':', 1, 1) + 1, instr(description, ';', 1, 1) - instr(description, ':', 1, 1) - 1) as name,
    to_number(substr(description, instr(description, ':', 1, 2) + 1, instr(description, ';', 1, 2) - instr(description, ':', 1, 2) - 1)) as cost,
    to_number(substr(description, instr(description, ':', 1, 3) + 1, instr(description, ';', 1, 3) - instr(description, ':', 1, 3) - 1)) as quantity
  from product;
end;

score 0 · Answer 2 · answered Sep 19 '13 at 19:34

0

Here's a sample query that will help you split the data out:

SELECT REGEXP_REPLACE(str,'.*NAME:([^;]+);.*','\1') AS name
  ,REGEXP_REPLACE(str,'.*COST:([^;]+);.*','\1') AS cost
  ,REGEXP_REPLACE(str,'.*QUANTITY:([^;]+);.*','\1') AS quantity
FROM SplitStringTest;

Here's a Fiddle to demonstrate. Regex is a very handy tool for this sort of thing.

Here are some references:

Regex tutorial

Oracle docs

answered Sep 19 '13 at 19:34

Emma

277
1
10

1

You're welcome! I hope it works for you. If so, please consider voting up the answers that helped and accepting the answer that worked. Best of luck! – Emma Sep 20 '13 at 14:15

Split one column value into multiple columns - stored procedure

2 Answers2