How can I select a list of distinct values from a field of comma separated values?

Question

I'm dealing with an old badly designed database in transition to a new system.

Products are associated with categories in the following way:

|------------|------------------|
| product_id |   category_ids   |
|------------|------------------|
| 12         |   34,52,105      |
|------------|------------------|
| 14         |   34,52,192      |
|------------|------------------|

I'm trying to figure out how to simply select a list of all unique category IDs from that column, like so:

|-------------|
| category_id |
|-------------|
| 34          |
|-------------|
| 52          |
|-------------|
| 105         |
|-------------|
| 192         |
|-------------|

Any help would be appreciated, thank you!

https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows - this shows how to parse the comma-separated column. "distinct" should de-dupe. — Neville Kuyt, Sep 18 '19 at 14:15

score 1 · Accepted Answer · answered Sep 18 '19 at 14:15

Here is a method that uses a an implicit conversion "trick" in MySQL:

select distinct substring_index(t.category, ',', -n) + 0
from (select 1 as n union all select 2 union all select 3
     ) n cross join
     t

This gets the nth element from the end. And then uses + 0 to convert to a number. The conversion stops at the comma, so this gets the nth value from the end.

You can extend the n derived table up to the maximum number of elements in a list.

How can I select a list of distinct values from a field of comma separated values?

1 Answers1