I have a column that stores a string representing a food order. The string goes as "102*4;109*3;101*2". Here the 3 digit represents the item code separated by '*' with the quantity while ';' acts as a separator among the item. I want to find a way separate the item code and quantity using SQL. I have done it before but used front end programming to separate it before entering the database
If you are using Oracle 12C then you can use regular expression to achieve your result. for example:-
create table food_order (order_no number, food_order varchar2(500));
insert into food_order values (1001,'102*4;109*3;101*2');
insert into food_order values (1002,'103*3;108*5;101*3');
insert into food_order values (1003,'106*4;107*3;109*2');
insert into food_order values (1004,'106*4;105*3;101*7');
select * from food_order;
Output:-
ORDER_NO FOOD_ORDER
1001 102*4;109*3;101*2
1002 103*3;108*5;101*3
1003 106*4;107*3;109*2
1004 106*4;105*3;101*7
Now try the below query:-
SELECT order_no,
regexp_substr(food_order,'[^*]+', 1,1) AS"ORDER_ID",
regexp_substr(food_order,'[^*]+', 1,2) AS"QUANTITY"
FROM
(SELECT DISTINCT order_no,
regexp_substr(food_order,'[^;]+', 1, level) AS"FOOD_ORDER"
FROM food_order
CONNECT BY regexp_substr(food_order, '[^;]+', 1, level) IS NOT NULL
) temp order by order_no;