SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

If the name column were a JSON array (like '["a","b","c"]'), then you could extract/unpack it with JSON_TABLE() (available since MySQL 8.0.4):

select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

Result:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

View on DB Fiddle

If you store the values in a simple CSV format, then you would first need to convert it to JSON:

select t.id, j.name
from mytable t
join json_table(
  replace(json_array(t.name), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) j

Result:

| id  | name |
| --- | ---- |
| 1   | a    |
| 1   | b    |
| 1   | c    |
| 2   | b    |

View on DB Fiddle

I have take the reference from here with changed column name.

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
  DECLARE output VARCHAR(65000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
  DECLARE i INTEGER;

  SET i = 1;
  REPEAT
    INSERT INTO GoodTable (id, name)
      SELECT id, strSplit(name, ',', i) FROM BadTable
      WHERE strSplit(name, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;