MySQL split comma separated values and GROUP BY result

Question

I have table as bellow.

t1

+---------+-------------+ 
| id      | value       |  
+---------+-------------+ 
| 1       |  A,B,C,D    | 
| 2       |  B,C,E,F    | 
| 3       |  E,F,G,H    | 
| 4       |  G,A,E,N    | 
+---------+-------------+

I want a query split value and then group by result. like

+-------+ 
|result |  
+-------+ 
|  A    | 
|  B    | 
|  C    | 
|  D    | 
+-------+

I try following but it's fail.

SELECT SUBSTRING_INDEX(value, ',', 1) as result
FROM t1 GROUP BY result

anyone to give better hint thanks

What do you mean by '_group by matched_'? `D` has no match anywhere yet it's included in your results — Martin, Nov 19 '19 at 08:49
doesn't deserve to give minus one, anyway I mean group by return result value. — ash, Nov 19 '19 at 09:03
I didn't give it -1, I was just asking for clarity about what you are asking — Martin, Nov 19 '19 at 09:04

Martin · Accepted Answer · 2019-11-19T09:46:58.680

1

I'm not 100% clear on what you are looking to output. I think you are saying that you want to return the values from the value column that occur more than once in any row in your comma-separated list.

In that case, the following query will produce the output you want:

SELECT  result
  FROM  (
        SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', a.letter + 1), ',', -1) result
          FROM  t1
            INNER JOIN (SELECT 0 letter UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a ON LENGTH(REPLACE(value, ',' , '')) <= LENGTH(value) - a.letter
        ) a
  GROUP BY result
  HAVING COUNT(result) > 1
  ORDER BY result

OUTPUT:

result
A
B
C
E
F
G

Why these results? Each of these letters occurs at least twice in the comma-separated lists. For example, A occurs twice (in row 1 and row 4), E occurs three times (in rows 2, 3, and 4), etc.

Note: This currently supports comma-separated lists of up to 4 letters only. It's not clear from your sample data whether this is enough or not.

Edit following OP comment

To get a list of all of the letters, change to use this SQL:

SELECT  result
  FROM  (
        SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', a.letter + 1), ',', -1) result
          FROM  t1
            INNER JOIN (SELECT 0 letter UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a ON LENGTH(REPLACE(value, ',' , '')) <= LENGTH(value) - a.letter
        ) a
  GROUP BY result
  ORDER BY result

Removing the HAVING COUNT(result) > 1 will cause all results to be returned.

edited Nov 19 '19 at 09:46

answered Nov 19 '19 at 08:55

Martin

16,093
1
29
48

as you said if letters occurs twice above query returning correct result but now what to do with values not occur twice? I need them to be listed in same result :( – ash Nov 19 '19 at 09:31
@ash So you just want all of the letters regardless of whether they occur once, twice, or more? – Martin Nov 19 '19 at 09:35
unfortunately yes – ash Nov 19 '19 at 09:38
@ash See the updated code – Martin Nov 19 '19 at 09:47
thank you that worked, I'm selecting this as accepted answer, but link https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad changed my mind I want to use separated value only for reading will change design for insert and update. – ash Nov 19 '19 at 09:52
@ash Thank you, and I think you've made the right choice. I'm glad to have helped but changing your data structure is definitely the best way forward – Martin Nov 19 '19 at 09:54

MySQL split comma separated values and GROUP BY result

1 Answers1