CODEWITHSUNDEEP
mysqlsqlstringcsvrecursive-query
jjj
jjj

Reputation: 2672

MySQL - select distinct values from a column where values are separated by comma

To get the list of distinct values from table1 and column1 is as easy as doing this:

SELECT distinct(column1) 
FROM table1

However, I inherited (unfortunately) a database where column1 contains values separated by a comma

column1
--------
row 1: name1,name2
row 2: name2,name3
row 3: name4,name1,name3

I need to get the list of distinct values from column1, so it looks like this:

column1
--------
name1
name2
name3
name4

Any ideas?

Upvotes: 0

Views: 1531

Answers (3)

GMB
GMB

Reputation: 222482

A generic method uses a recursive query (available in MySQL 8.0 only):

with recursive 
    data as (select concat(column1, ',') rest from mytable),
    words as (
        select substring(rest, 1, locate(',', rest) - 1) word, substring(rest, locate(',', rest) + 1) rest
        from data
        union all
        select substring(rest, 1, locate(',', rest) - 1) word, substring(rest, locate(',', rest) + 1) rest
        from words
        where locate(',', rest) > 0
)
select distinct word from words order by word

Demo on DB Fiddle

Sample data:

| column1           |
| :---------------- |
| name1,name2       |
| name2,name3       |
| name4,name1,name3 |

Results:

| word  |
| :---- |
| name1 |
| name2 |
| name3 |
| name4 |

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You have to split them apart. If you have at most three names in the column, then one method is:

select substring_index(column1, ',', 1) as name
from t
union -- on purpose to remove duplicates
select substring_index(substring_index(column1, ',', 2), ',', -1) as name
from t
where name like '%,%'
union -- on purpose to remove duplicates
select substring_index(substring_index(column1, ',', 3), ',', -1) as name
from t
where name like '%,%,%';

Upvotes: 1

Nilesh PS
Nilesh PS

Reputation: 366

You can't. Your database does not adhere to the the first principle in designing normalized databases :- Atomicity. It says to store one and only one attribute in a column and yet you have so many. You need to retrieve the entire columns value, split and de-dupe them from your application. SQL cannot do this for you.

What you really need to do here is to have a seperate NAMES table and apply DISTINCT on name column after filtering relevant rows.

Upvotes: 0

Related Questions