I have some data which contains a comma separated list and three columns. What I'd like is to find the values that exist in the list but not in any column.
DATA:
+-------+-------+--------+-----------------+
| col_a | col_b | col_c | list |
+-------+-------+--------+-----------------+
| red | NULL | green | yellow,blue |
| red | blue | green | red,blue,green |
| red | NULL | yellow | green |
| blue | white | green | red,white,green |
| NULL | NULL | NULL | red,blue,green |
+-------+-------+--------+-----------------+
DESIRED OUTPUT:
+-------+-------+--------+-----------------+----------------+
| col_a | col_b | col_c | list | unmatched |
+-------+-------+--------+-----------------+----------------+
| red | NULL | green | yellow,blue | yellow,blue |
| red | blue | green | red,blue,green | |
| red | NULL | yellow | green | green |
| blue | white | green | red,white,green | red |
| NULL | NULL | NULL | red,blue,green | red,blue,green |
+-------+-------+--------+-----------------+----------------+
At the moment I have a query that works but it's very ugly:
SELECT *,
TRIM(
BOTH ',' FROM
REPLACE(
REPLACE(
REPLACE(
REPLACE(
LIST,
IFNULL(col_a,''),
''
),
IFNULL(col_b,''),
''
),
IFNULL(col_c,'')
,''
),
',,',
','
)
) AS unmatched
FROM color_list
Is there a cleaner way to find the unmatched values? The thing I'm doing here would be really hard to scale and feels quite awkward and fragile.