I have two columns (Please check the image) based on which I want to create a third column stating that they are a 'match'

However, both the columns are off a 'picklist' data type and I am not sure how to perform that in mysql.

P.S Both the columns have multiple entries with a delimiter as ';', so the logic go true in both cases. col 1 to col 2 and col 2 to col 1

I tried using SELECT col 2 IN (SELECT col 1 from table 1) FROM table 2 however, it only works on some records (strange)

CodePudding user response：

If you have a list of values in either (but not both) col1 or col2 then you can do:

select
  col1,
  col2,
  find_in_set(col1, replace(col2, ';', ',')) or
  find_in_set(col2, replace(col1, ';', ','))
from t;