How to Split Comma-Separated Values into Rows in MySQL?

Transforming Comma-Separated Data in MySQL

This guide explains how to efficiently convert comma-separated values within a MySQL column into individual rows. We'll leverage MySQL functions and a clever technique:

1. Creating a Number Sequence: First, generate a temporary table (or use an existing one) containing a sequence of integers. This sequence should be larger than the maximum number of comma-separated values in your target column. This sequence will act as an index for extracting individual values.

2. Leveraging SUBSTRING_INDEX: The SUBSTRING_INDEX function is key here. It extracts substrings based on a delimiter and a specified occurrence. We'll use it twice, in conjunction with the number sequence, to isolate each value.

Here's an example illustrating this approach:

<code class="language-sql">SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) AS value
FROM table1 t
CROSS JOIN (SELECT a.N + b.N * 10 + c.N * 100 AS 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
    ,(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) c
) n
WHERE n.n <= LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')) + 1;</code>

Explanation:

• The nested SELECT statement creates a number sequence (adjust as needed for your data).
• CROSS JOIN combines this sequence with your table (table1).
• SUBSTRING_INDEX(t.values, ',', n.n) extracts the substring up to the nth comma.
• SUBSTRING_INDEX(..., ',', -1) then extracts the value from the previous result to the end of the string.
• WHERE clause ensures we only process values within the bounds of the comma-separated string. The condition LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')) 1 dynamically calculates the number of values.

This method effectively splits your comma-separated values into individual rows, providing a clean and efficient solution for data manipulation in MySQL. Remember to replace table1 and values with your actual table and column names.

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