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

Split comma separated rows in SQL

Question:

How to split a column containing comma separated values ​​into separate rows in SQL (MySQL)?

Answer:

MySQL does not provide a built-in function for splitting comma-separated row values. However, you can use an unconventional method to achieve this, which involves counting numbers and MySQL's SUBSTRING_INDEX() function:

<code class="language-sql">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;</code>

This query uses the SUBSTRING_INDEX() function to extract individual values ​​by specifying a comma as the delimiter and an n value as the split position. Count numbers are generated using a subquery that creates a range of numbers from 1 to 100, allowing you to split up to 100 separated values ​​per row.

For the persistent count table method, use the following query:

<code class="language-sql">SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
FROM table1 t
CROSS JOIN tally n
WHERE n.n <= LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')) + 1;</code>

This method has better scalability if you need to split a large number of rows containing comma separated values.

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