How do I sort a table with non alphabetical values?

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

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;

Demo fiddle