How to Extract Values from Comma-Separated Strings in MySQL?

MySQL String Extraction from Comma-Separated Values

In MySQL, it is often necessary to extract individual values from a string that contains multiple values separated by commas. This poses a challenge as MySQL does not natively provide a string split function.

To address this, we can leverage a stored procedure to iteratively extract values. Here's how:

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 ;

Sample Data:

Col1	Col2
1	a,b,c
2	d,e

Result:

Col1	Col2
1	a
1	b
1	c
2	d
2	e

Usage:

To use this solution, create a table named BadTable with the original data and a new table named GoodTable to store the extracted values. Then, execute the following commands:

CALL BadTableToGoodTable();
SELECT * FROM GoodTable;

The above is the detailed content of How to Extract Values from Comma-Separated Strings in MySQL?. For more information, please follow other related articles on the PHP Chinese website!