根据REGEX将mysql列值拆分为2列

Question

i have a table that looks like this

ID   | VALue   |
1    | Cap 120 |
1    | Sack 100 |
3    | etc.. 100 |

and i need to move digits in this column to a single column of its own so for above example it would be like

ID   | VALue    |  number |
1    | Cap  | 120     |
1    | Sack | 100     |
3    | etc..| 50      |

is it possible with mysql ? or do i Have to do an php solution to fix my table ?

thanks in advance

Answer 1

This will handle more than one space, example "Cap Dap" (without chopping it to just "Cap")

select 
substring_index(value,' ',length(value) - length(replace(value, ' ', ''))) as value, 
substring_index(value,' ', -1) as num from tbl;

Example, with a row having more than one spaces: http://sqlfiddle.com/#!2/9dc2f/5/0