Picklist data sorting in MySQL

Question

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)

Can there be lists on both sides? If not then you can use replace() to change ; to , then to get a strlist, then use find_in_set(col1, replace(col2, ';', ',')) and the same thing with col1 and col2 switched. — user9706
– user9706, Commented Oct 25, 2021 at 20:15
please read up on stackoverflow.com/questions/3653462/… and meta.stackoverflow.com/questions/285551/… — nbk
– nbk, Commented Oct 25, 2021 at 20:16
@Swas thanks, but you lost rows so data no longer match the "In the above example rows 2,3,5,8 ..." — user9706
– user9706, Commented Oct 25, 2021 at 20:21

user9706 · Accepted Answer · 2021-10-25 20:34:24Z

1

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;

answered Oct 25, 2021 at 20:34

user9706

Sign up to request clarification or add additional context in comments.

2 Comments

Swas Over a year ago

Thank you for the code Allan. Yes, they are a list of values. I was trying the exact solution that you provided above, in the meantime. I am just running the code to see the results.

Swas Over a year ago

Hi Allan, I am trying to create a case statement based on the above solution which mentions the 'match' and 'unmatched'. I would surely accept it once its done

Collectives™ on Stack Overflow

Picklist data sorting in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related