How to Split Comma-Separated Values into Multiple Rows in SQL?

Splitting Values into Multiple Rows in SQL

One common task in data processing is to split a comma-separated string into multiple rows, as exemplified by the following request:

Problem:

Given a table with an id column and a name column containing comma-separated values, return the data in the following format:

id	name
1	a
1	b
1	c
2	b

Solution:

To achieve this, we can leverage MySQL's SUBSTRING_INDEX function in conjunction with a numbers table that contains a sequence of numbers up to the maximum number of fields to split. The query below demonstrates this approach:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) AS name
FROM
  numbers INNER JOIN tablename
  ON CHAR_LENGTH(tablename.name) - CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
ORDER BY
  id, n

If creating a numbers table is not feasible, we can use the following query to generate the sequence of numbers within the query itself:

SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) AS name
FROM
  (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5
  ) AS numbers INNER JOIN tablename
  ON CHAR_LENGTH(tablename.name) - CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
ORDER BY
  id, n

Demo:

[SQL Fiddle Demo](https://www.sqlfiddle.com/#!5/cf4386/1)

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