CODEWITHSUNDEEP
sqloracle-databasestored-procedures
user2796551
user2796551

Reputation: 1

Split one column value into multiple columns - stored procedure

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

Upvotes: 0

Views: 1776

Answers (2)

Emma
Emma

Reputation: 277

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

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;

Upvotes: 1

Related Questions