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) namefrom numbers inner join tablename on CHAR_LENGTH(tablename.name) -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1order 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) namefrom (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-1order 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.namefrom mytable tjoin json_table( t.name, '$[*]' columns (name varchar(50) path '$')) j;
Result:
| id | name || --- | ---- || 1 | a || 1 | b || 1 | c || 2 | b |
If you store the values in a simple CSV format, then you would first need to convert it to JSON:
select t.id, j.namefrom mytable tjoin json_table( replace(json_array(t.name), ',', '","'), '$[*]' columns (name varchar(50) path '$')) j
Result:
| id | name || --- | ---- || 1 | a || 1 | b || 1 | c || 2 | b |
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 ;