I’m a non-programmer and want to use the power of SQL to sort my inventory data. Here’s the sample table that I have.
Product | Option_1_Value Apple | 1 gallon Apple | Apple Apple | 30ml Apple | 4 gallons Apple | 10ml Apple | 100ml Apple | 500ml Apple | 1 liter Banana| 4 gallons Banana| 1 gallon Banana| Banana Banana| 10ml Banana| 100ml Banana| 500ml Banana| 1 liter
I want to turn this data to this. How can I query my table to have a result like this below
Product | Option_1_Value Apple | Apple Apple | 10ml Apple | 30ml Apple | 100ml Apple | 500ml Apple | 1 liter Apple | 1 gallon Apple | 4 gallons Banana| Banana Banana| 10ml Banana| 100ml Banana| 500ml Banana| 1 liter Banana| 1 gallon Banana| 4 gallons
Advertisement
Answer
These two suggestions – if stick to the datasets given.
If you’re on MySQL 8++ or MariaDB 10.3+, you can use REGEXP_REPLACE like this:
SET @gal := 3.78541, @lit := 1000; -- setting variable value for gallon & liter.
SELECT Product,option_1_value,
(CASE WHEN size LIKE '%gallon%' THEN qty*@gal*@lit WHEN size LIKE '%liter%' THEN qty*@lit ELSE qty END)+0 AS size_in_ml
FROM
(SELECT *,
REGEXP_REPLACE(option_1_value,'[A-Za-z]','') AS Qty,
TRIM(REGEXP_REPLACE(option_1_value,'[0-9]','')) AS Size
FROM myproduct) p
ORDER BY product ASC, size_in_ml ASC;
If you’re using older MySQL version:
SET @gal := 3.78541, @lit := 1000; -- setting variable value for gallon & liter.
SELECT Product,option_1_value,
(CASE WHEN size LIKE '%gallon%' THEN qty*@gal*@lit WHEN size LIKE '%liter%' THEN qty*@lit ELSE qty END)+0 AS size_in_ml
FROM
(SELECT *,
REPLACE(REPLACE(REPLACE(REPLACE(option_1_value,'gallons',''),'gallon',''),'ml',''),'liter','')+0 AS Qty,
SUBSTRING(option_1_value,LEAST(IF(LOCATE('g',option_1_value)=0,99,LOCATE('g',option_1_value)),
IF(LOCATE('l',option_1_value)=0,99,LOCATE('l',option_1_value)),
IF(LOCATE('m',option_1_value)=0,99,LOCATE('m',option_1_value)))) AS size FROM myproduct) p
ORDER BY product ASC, size_in_ml ASC;