How Can MySQL\'s strSplit Function Transform Comma-Separated Strings into Multiple Rows?

Splitting Strings in MySQL with strSplit Function

Unlike the question in the duplicate thread, this question specifically asks how to split a comma-separated string column into multiple rows. To achieve this, MySQL provides a versatile stored procedure that leverages the strSplit() function.

The strSplit() function breaks down a given string (x) based on a specified delimiter (delim) and position (pos). It does so by extracting the substring from position pos of the delimited string, replacing the delimiter with an empty string, and returning the resulting substring.

To illustrate, let's consider the input table:

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

To transform this into the desired output:

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

We define the BadTableToGoodTable() stored procedure as follows:

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 $$

Here, we loop through each character in the comma-separated string of Col2 using the REPEAT statement, extracting the substring using strSplit() and inserting it into a new table (GoodTable).

By following these steps, we can effectively split strings in MySQL, allowing us to reshape comma-separated columns into more normalized data structures.

The above is the detailed content of How Can MySQL\'s strSplit Function Transform Comma-Separated Strings into Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!