Search code examples
mysqlpicklist

Picklist data sorting in MySQL

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

col 1 col 2 Match column
MA;NY NY Match
MA;NY FL Un-match
KS AR;KY;LA;MS Un-Match
KY AR;KY;LA;MS 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)

Solution

  • 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;