0

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)

Swas
  • 49
  • 6
  • Please post data as text instead of an image. – Allan Wind Oct 25 '21 at 20:12
  • 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. – Allan Wind Oct 25 '21 at 20:15
  • please read up on https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question – nbk Oct 25 '21 at 20:16
  • @AllanWind I have updated the post with text – Swas Oct 25 '21 at 20:20
  • @Swas thanks, but you lost rows so data no longer match the "In the above example rows 2,3,5,8 ..." – Allan Wind Oct 25 '21 at 20:21
  • @AllanWind ya, I just deleted them and appended them in the 3rd column – Swas Oct 25 '21 at 20:25
  • If you have lists on both col1 and col2, then you probably need to expand a given row into n rows. There are existing questions covering. https://stackoverflow.com/questions/12872277/split-comma-separated-value-from-table-column-into-rows-using-mysql – Allan Wind Oct 25 '21 at 20:29

1 Answers1

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;
Allan Wind
  • 23,068
  • 5
  • 28
  • 38
  • 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 Oct 25 '21 at 20:48
  • 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 – Swas Oct 26 '21 at 13:08