Redshift LIKE column value with %

Question

I have a column that is a comma separated string of values. I want to join another table that only has one of the values. On redshift, how can I do a LIKE operator with '%' injected into the comparison?

Ex:

TableA: values_col = 'abc, def'

TableB: value_col = 'def'

SELECT *
FROM TableA a
JOIN TableB b ON b.value_col LIKE '%' || a.values_col || '%'

The above concat doesn't seem to work. Any suggestions would be appreciated. Thanks.

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-02-13T23:16:15

You will get awful performance. You should fix your data structure. But if you have to, then this should work:

SELECT *
FROM TableA a JOIN
     TableB b
     ON ',' || a.values_col || ',' LIKE '%,' || b.value_col || ',%';

The commas are important if your values can contain each other. More importantly, the like needs the operands in the right order.

Redshift LIKE column value with %

1 Answers