title: MySQL split multiple value into multiple rows
body: <p>I need one help on splitting multiple values from multiple columns into another column. Below is an example</p>
  
  <pre><code>CREATE TABLE split
  (
      `Col_1`             VARCHAR(120),
      `Col_2`                 VARCHAR(50),
      `Col_3`                VARCHAR(20),
      `Col_4`                 VARCHAR(50)
  );
  
  Insert into split (Col_1,Col_2,Col_3,Col_4) values ('ABC','1','10',null);
  Insert into split (Col_1,Col_2,Col_3,Col_4) values ('DEF','2,3','30,40',null);
  Insert into split (Col_1,Col_2,Col_3,Col_4) values ('GHI','4,5','50','500,600,700');
  
  select * from split;
  
  +-------+-------+-------+-------------+
  | Col_1 | Col_2 | Col_3 | Col_4       |
  +-------+-------+-------+-------------+
  | ABC   | 1     | 10    | NULL        |
  | DEF   | 2,3   | 30,40 | NULL        |
  | GHI   | 4,5   | 50    | 500,600,700 |
  +-------+-------+-------+-------------+
  </code></pre>
  
  <p>I am no expert in this, but have been playing around and have managed to split only col_2 into multiple rows as below:</p>
  
  <pre><code>SELECT
    Col_1,Col_2,Col_3,Col_4,
    SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_2, ',', numbers.n), ',', -1) Col_2_NEW,
    SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_3, ',', numbers.n), ',', -1) Col_3_NEW,
    SUBSTRING_INDEX(SUBSTRING_INDEX(split.Col_4, ',', numbers.n), ',', -1) Col_4_NEW
  FROM
    (SELECT 1 n UNION ALL SELECT 2
     UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN split
    ON CHAR_LENGTH(split.Col_2) - CHAR_LENGTH(REPLACE(split.Col_2, ',', ''))&gt;=numbers.n-1
  ORDER BY Col_2, n;
  
  +-------+-------+-------+-------------+-----------+-----------+-----------+
  | Col_1 | Col_2 | Col_3 | Col_4       | Col_2_NEW | Col_3_NEW | Col_4_NEW |
  +-------+-------+-------+-------------+-----------+-----------+-----------+
  | ABC   | 1     | 10    | NULL        | 1         | 10        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 2         | 30        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 3         | 40        | NULL      |
  | GHI   | 4,5   | 50    | 500,600,700 | 4         | 50        | 500       |
  | GHI   | 4,5   | 50    | 500,600,700 | 5         | 50        | 600       |
  +-------+-------+-------+-------------+-----------+-----------+-----------+
  </code></pre>
  
  <p>However, I would like to split, col_3 and col_4 into new as well, so it gives me below output.</p>
  
  <pre><code>+-------+-------+-------+-------------+-----------+-----------+-----------+
  | Col_1 | Col_2 | Col_3 | Col_4       | Col_2_NEW | Col_3_NEW | Col_4_NEW |
  +-------+-------+-------+-------------+-----------+-----------+-----------+
  | ABC   | 1     | 10    | NULL        | 1         | 10        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 2         | 30        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 2         | 40        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 3         | 30        | NULL      |
  | DEF   | 2,3   | 30,40 | NULL        | 3         | 40        | NULL      |
  | GHI   | 4,5   | 50    | 500,600,700 | 4         | 50        | 500       |
  | GHI   | 4,5   | 50    | 500,600,700 | 4         | 50        | 600       |
  | GHI   | 4,5   | 50    | 500,600,700 | 4         | 50        | 700       |
  | GHI   | 4,5   | 50    | 500,600,700 | 5         | 50        | 500       |
  | GHI   | 4,5   | 50    | 500,600,700 | 5         | 50        | 600       |
  | GHI   | 4,5   | 50    | 500,600,700 | 5         | 50        | 700       |
  +-------+-------+-------+-------------+-----------+-----------+-----------+
  </code></pre>
  
  <p>I have searched all over and so far, they are splitting the row into one column only and have not been able to find problem something similar to mine. 
  Maybe some join is missing or some union, I don't know as I am not good at queries. 
  Can anyone help me here? without asking me to read guide or handbooks :-)</p>
  
  <p>Thanks in advance</p>