regex - Mysql: Split one column with number and letter to two -

i have table this:

product|weight      |5kg b      |5.2kg c      |3.9kg d      |6l 

i split second columns have table looking this:

   product|weight|unit          |5    |kg     b      |5.2  |kg     c      |3.9  |kg     d      |6    |l 

how do it? found answer here split value 1 field two , seems work if have separator.

for data show in example:

crate table foo (product varchar(7), weight varchar(7)); insert foo values ('a','5kg'),('b','5.2kg'),('c','3.9kg'),('d','6l') 

this work:

select t.product      , t.weight      , t.weight + 0 num      , replace(t.weight,t.weight+0,'') unit   foo t 

but...

this won't work in more general case, consider:

insert foo values ('e','1.00kg'),('f','02m'),('g','0kg'),('h','10ppm10') 

the "trick" query using evaluate column weight in numeric context, adding zero. mysql return numeric value.

what doesn't work getting units portion. in special case numeric value converted string matches leading string in weight column, replace function can used replace string 0 length string. have same problem substring , char_length function; don't have solution.

---

a better approach getting "unit" may compare specified list of units.

     , case        when t.weight '%kg'  'kg'        when t.weight '%g'   'g'        when t.weight '%ml'  'ml'        when t.weight '%l'   'l'        else ''        end unit 

using approach, it's important check "longest" strings before shorter strings (e.g. if checked '%l' before check '%ml', we'd 'l' returned rather 'ml').

note mysql regexp operator limited, returns boolean; indicating whether expression matches pattern, won't return location or portion of string matched pattern.