How to Reverse MySQL's GROUP_CONCAT: Splitting a Concatenated String into Individual Values?

Unraveling the Enigma of GROUP_CONCAT's Inverse in MySQL

In the realm of data manipulation, the GROUP_CONCAT function reigns supreme in concatenating multiple values into a single string. However, the inverse operation, splitting a concatenated string into individual values, poses a challenge.

The Problem at Hand

Consider a scenario where you have a table "colors" with data structured as:

+----+----------------------+
| id | colors               |
+----+----------------------+
| 1  | Red,Green,Blue       |
| 2  | Orangered,Periwinkle |
+----+----------------------+

Your goal is to transform this data into a more granular format:

+----+------------+
| id | colors     |
+----+------------+
| 1  | Red        |
| 1  | Green      |
| 1  | Blue       |
| 2  | Orangered  |
| 2  | Periwinkle |
+----+------------+

The Solution: Diving into the Depths of SQL

To achieve this data metamorphosis, you can harness the power of the SUBSTRING_INDEX function. This versatile function allows you to extract specific substrings based on delimiters.

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
  colors
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY
  id,
  n.digit

Unveiling the Mechanics behind the Query

The outer query selects the "id" column and employs the SUBSTRING_INDEX function twice nested to isolate the desired substrings. The first invocation of SUBSTRING_INDEX extracts the substring up to the (n.digit 1)-th comma, while the second invocation then extracts the substring after the (n.digit 1)-th comma to the end of the string.

The INNER JOIN with the subquery ensures that the SUBSTRING_INDEX function is applied for a maximum of 4 substrings, as defined by the "digit" column in the subquery. To support more substrings, adjust the subquery accordingly.

Finally, the ORDER BY clause sorts the results by "id" and then by the numerical sequence of substrings for each row.

Demo and Credits

For a practical demonstration, visit the fiddle linked in the reference provided.

References

[Extract Multiple Values from a Comma Separated List](https://www.data-generator.com/fiddle/d3c78f201137b4b56a63a95332b8106b)

The above is the detailed content of How to Reverse MySQL's GROUP_CONCAT: Splitting a Concatenated String into Individual Values?. For more information, please follow other related articles on the PHP Chinese website!