How Can I Split Comma-Separated Values in a MySQL String Column into Multiple Rows?

Mysql String Split: Expand Rows with Comma-Separated Values

In this question, the user seeks a solution to convert a table containing one string column with comma-separated values into a new table with multiple rows, splitting the values by commas. While the question claims not to be an exact duplicate of an existing thread, it shares similarities.

To address this requirement, a stored procedure can be employed:

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 (col1, col2)
      SELECT col1, strSplit(col2, ',', i) FROM BadTable
      WHERE strSplit(col2, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;

This stored procedure initializes an integer variable i. It iterates through each string value in the col2 column, using the strSplit function to extract the corresponding substring at position i. The extracted substring represents one of the comma-separated values.

The procedure continues, incrementing i for each iteration, until there are no more non-null substrings. This ensures that all comma-separated values are split and inserted into the new table.

In summary, this stored procedure provides a robust solution to convert a table with comma-separated values into a table with multiple rows, making it easier to manipulate and analyze the data.

The above is the detailed content of How Can I Split Comma-Separated Values in a MySQL String Column into Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!