2

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.

cvax
  • 416
  • 1
  • 5
  • 12

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. What do you suggest for the data structure? Should I flatten the list into n-columns then or just have them be different rows? Thanks. – cvax Feb 14 '18 at 19:06
  • @cvax . . . You should not store multiple values in a delimited list. You should have one row per value. – Gordon Linoff Feb 15 '18 at 03:23