Mysql: Split one column with number and letter to two

Question

I have a table like this:

Product|weight
A      |5kg
B      |5.2kg
C      |3.9kg
D      |6l

I would like to split the second columns to have a table looking like this:

   Product|weight|unit
    A      |5    |kg
    B      |5.2  |kg
    C      |3.9  |kg
    D      |6    |l

How do I do it? I found an answer here Split value from one field to two , but this seems only to work if you have a separator.

Do you know all the different defined units (like kg/l)? If so, you can use http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate to locate the character index to start the split. — Louis Ingenthron, Apr 09 '14 at 16:19

score 1 · Answer 1 · answered Apr 09 '14 at 16:36

For just the data you show in the example:

CRATE TABLE foo (product varchar(7), weight varchar(7));
INSERT INTO foo VALUES ('A','5kg'),('B','5.2kg'),('C','3.9kg'),('D','6l')

This will work:

SELECT t.product
     , t.weight
     , t.weight + 0 AS num
     , REPLACE(t.weight,t.weight+0,'') AS unit
  FROM foo t

BUT...

This won't work in the more general case, consider:

INSERT INTO foo VALUES ('E','1.00kg'),('F','02m'),('G','0kg'),('H','10ppm10')

The "trick" the query is using is to evaluate the column weight in a numeric context, by adding a zero. MySQL will return the numeric value.

What doesn't really work is getting the units portion. In the special case where the numeric value converted to a string exactly matches the leading string in the weight column, the REPLACE function can be used to replace that string with a zero length string. We have the same problem with SUBSTRING and the CHAR_LENGTH function; I don't have a good solution.

A better approach to getting the "unit" may be to compare to a specified list of units.

     , CASE
       WHEN t.weight LIKE '%kg'  THEN 'kg'
       WHEN t.weight LIKE '%g'   THEN 'g'
       WHEN t.weight LIKE '%ml'  THEN 'ml'
       WHEN t.weight LIKE '%l'   THEN 'l'
       ELSE ''
       END AS unit

Using this approach, it's important to check for the "longest" strings before the shorter strings (e.g. if we checked for '%l' before we check for '%ml', we'd get 'l' returned rather than 'ml').

NOTE The MySQL REGEXP operator is limited, it only returns a boolean; indicating whether an expression matches a pattern, it won't return a location or a portion of the string that matched the pattern.

Mysql: Split one column with number and letter to two

1 Answers1