Search code examples
mysqlfind-in-set

how to check if value from a comma-separated string exists in string MySQL

I have a table with a column named tags. In this column, I have a comma-separated list like air conditioner, tv, fridge.

I want to check if any of the words in the comma-separated column exists in a string like

Is there an air conditioner in the room?

So, the query should return a match as in the column tags, the air conditioner value exists.

I was thinking of using find_in_set, but it will not work in this case as the logic should be reversed.

select * from products where find_in_set('Is there an air conditioner in the room?',tags) <> 0

UPDATE

I could use find_in_set by separating the string into words and then checking against the tags column, but it will not work for a combination like "air conditioner" as it will spit like air, conditioner.

MySQL version 5.7.44

Solution 
  • SELECT * FROM products
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(tags, ', *', '","'), '"]'),
  '$[*]' COLUMNS(tag VARCHAR(20) PATH '$')
) AS j
WHERE LOCATE(j.tag, 'Is there an air conditioner in the room?');

    Demo: https://dbfiddle.uk/ayNuWEqQ

    This cannot be optimized with an index, just like any other substring-matching task.

    The above solution with JSON_TABLE() requires MySQL 8.0 or later. MySQL 5.x is now past its end-of-life, but I understand some people have not upgraded. I'll leave this answer here for future folks who read this thread.