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?

Answer

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 |