mysql select distinct comma delimited values

Question

i have a mysql table

id      cid    c_name       keywords
 1       28    Stutgart    BW,Mercedes,Porsche,Auto,Germany
 2       34    Roma        Sezar,A.S. Roma
 3       28    München     BMW,Oktober Fest,Auto,Germany

i need a query to show keywords from cid=28 but i want to see only 1 time a keyword, like (BW,Mercedes,Porsche,Auto,Bmw,Oktober Fest,Germany) i dont want to list 2 time a keyword, how can resolve this problem?

i have tried distinct but could not get what i want

You should normalize your data model. Storing comma separated values is a **very** bad idea. — , Jun 14 '17 at 14:21
thinking to normalize my db but it will take long time for me.. 98k entry :( — Berdan, Jun 14 '17 at 17:27

score 8 · Accepted Answer · answered Jun 14 '17 at 14:49

8

Split it before adding it all up with DISTINCT.Of course,better is to normalize your data(no more than 1 value in a column)

SELECT
  GROUP_CONCAT( DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(keywords, ',', n.digit+1), ',', -1)) keyword
FROM
  t
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(keywords, ',' , '')) <= LENGTH(keywords)-n.digit
WHERE cid=28

See it working

answered Jun 14 '17 at 14:49

Mihai

26,325
7
66
81

how can i print this query :( thinking to normalize my db but it will take long time for me.. 98k entry :( – Berdan Jun 14 '17 at 17:25
@Berdan I dont understand what you mean by print this query. – Mihai Jun 14 '17 at 18:00
1

i used explode and split, i got what i want, but thank you. – Berdan Jun 16 '17 at 08:51
@Mihai Can you please explain your query? It's great but I can't understand it properly. Please explain the use of each function in this query. – Pinal Patel Feb 06 '19 at 06:21

Pinal Patel · Answer 2 · 2019-02-09T05:28:31.803

1

If you want to get a dynamic output then you can use the following query to get a distinct comma delimited values in a single record.

Note: here doesn't matter how many values are in comma delimited row & it's fetched distinct record from a number of rows based on your condition

$tag_list = DB::select('SELECT
                          TRIM(TRAILING "," FROM REPLACE(GROUP_CONCAT(DISTINCT keywords, ","),",,",",")) tag_list
                          FROM
                          test
                          WHERE id = 28');

$unique_tags = implode(',', array_unique(explode(",",$result[0]->search_tags)));

edited Feb 09 '19 at 05:28

answered Feb 06 '19 at 10:46

Pinal Patel

122
1
16

additional info: keywords = your field name | tag_list = query alias | test = your table | WHERE condition is optional – Abid Khairy Jan 18 '22 at 05:43

mysql select distinct comma delimited values

2 Answers2

Linked