How to Convert Comma-Separated Values into Individual Rows in MySQL?

Transforming Comma-Separated Values into Individual Rows in MySQL

This guide demonstrates how to convert comma-separated values within a MySQL column into individual rows. Let's say you have a table with a column containing comma-delimited data like this:

<code>somethingA,somethingB,somethingC
somethingElseA, somethingElseB</code>

The objective is to restructure this data into:

<code>somethingA
somethingB
somethingC
somethingElseA
somethingElseB</code>

This SQL query achieves this transformation:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
FROM table1 t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n.n <= LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')) + 1;

Explanation:

This query cleverly uses a dynamically generated numbers table (the subquery) and the SUBSTRING_INDEX() function. SUBSTRING_INDEX() extracts substrings based on a delimiter. The numbers table provides the index for each comma-separated value. The WHERE clause ensures the query only processes values up to the actual number of comma-separated elements in each row.

Key Points:

• SUBSTRING_INDEX(string, delimiter, count): This function is crucial. string is the input, delimiter is the comma, and count specifies which occurrence of the delimiter to use. A count of -1 gets everything after the last occurrence of the delimiter.
• The subquery creates a temporary numbers table. You could replace this with a pre-existing numbers table for improved performance if you perform this type of operation frequently.
• The WHERE clause dynamically adjusts the number of rows returned based on the count of commas in the original string, preventing errors.

This method effectively splits comma-separated values into individual rows, resulting in a cleaner and more manageable dataset.

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