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, ',' , '')) <p><strong>Unveiling the Mechanics behind the Query</strong></p><p>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.</p><p>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.</p><p>Finally, the ORDER BY clause sorts the results by "id" and then by the numerical sequence of substrings for each row.</p><p><strong>Demo and Credits</strong></p><p>For a practical demonstration, visit the fiddle linked in the reference provided.</p><p><strong>References</strong></p><p>[Extract Multiple Values from a Comma Separated List](https://www.data-generator.com/fiddle/d3c78f201137b4b56a63a95332b8106b)</p>

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!