title: MySql- split cell and insert into another table
body: <p>I have a table <b>test</b> that has a column <i>state_numbers</i></p>
  
  <pre>id  state_numbers
  --  ------------
  1  scr:1.0043;smb:0;rsm:0
  2  scr:1.12;smb:0;rsm:0.3
  3  scr:0.083;smb:0.65;rsm:0
  4  scr:1.021;smb:0;rsm:0.2
  5  scr:0.77;smb:0;rsm:0</pre>
  
  <p>I have created a table called <b>Status</b> that looks like</p>
  
  <pre> id     scr    smb    rsm   total
  ------ ------ ------ ------ ------
  (null) (null) (null) (null) (null)</pre>
  
  <p>I need a query that would read the value in <i>state_numbers</i>, split the expression based on the keyword and put the values in corresponding columns of Status table.</p>
  
  <p>I'm expecting :</p>
  
  <pre> id     scr    smb    rsm   total
  ------ ------ ------ ------ ------
  1      1.0043   0      0    (null)
  2      1.12     0     0.3   (null)
  3      0.083   0.65    0    (null)</pre>
  
  <p>Then I will be able to add the data in each column and update in <i>total</i> column. </p>
  
  <p>Final Output:</p>
  
  <pre> id     scr    smb    rsm   total
  ------ ------ ------ ------ ------
  1      1.0043   0      0    1.0043
  2      1.12     0     0.3   1.42
  3      0.083   0.65    0    0.733</pre>
  
  <p>How would I achieve this? help me in splitting the particular cell and inserting the data in different fields.</p>
  
  <p>Thanks in advance!!! </p>
  
  <p>P.S: pls suggest a proper title for this question</p>