How Can I Reverse MySQL's GROUP_CONCAT to Separate Concatenated Data?

Reversing the Effects of GROUP_CONCAT: Splitting Concatenated Data

In MySQL, the GROUP_CONCAT function combines multiple values from a specific column into a single, comma-separated string. However, there may be instances where you desire the opposite effect, where a concatenated string is broken back down into its individual components.

The Query to Achieve the Splitting

To accomplish this, utilize the following query:

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

Understanding the Details

• SUBSTRING_INDEX Function: This function extracts the nth substring from a string, beginning at the first occurrence of a specified delimiter. In this query, it is used multiple times to isolate each color substring.
• UNION Operator: The UNION operator combines the results of multiple SELECT statements into a single table. Here, it returns a series of numbers representing the positions of the color substrings.
• digit 1: This adds 1 to the digit value to skip the comma delimiter at the beginning of each color substring.
• LENGTH Function: This function calculates the length of a string. Here, it is used to ensure that the digit value is within the range of the colors string's length.

Example Data and Output

For the sample data provided:

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

The output of the query will be:

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

The above is the detailed content of How Can I Reverse MySQL's GROUP_CONCAT to Separate Concatenated Data?. For more information, please follow other related articles on the PHP Chinese website!