MySQL string 'contains' functionality

Question

Yes, this is a question that has been beaten to death, yet I believe its a tad different.

Please consider this MySQL table test:

The desired resultset is:

In words, how can the first 3 records (ids 1, 2, 5) be selected using exactly this string - 'Cinema, Entertainment' and in one single query? That is, how can A be compared with the string above and have it return the record if even one 'word' matches?

Have tried the following:

• SELECT * FROM test WHERE A LIKE "%Cinema, Entertainment%"
• SELECT * FROM test WHERE INSTR(A, 'Cinema, Entertainment') > 0

Both return just the first record, doing an exact match.

SELECT * FROM test WHERE A LIKE '%Cinema%' OR A LIKE '%Entertainment%' does work, but I do not want to tokenize the available string. The string could also have multiple CSVs.

Very new to regular expressions, and cannot seem to form the right query.

Have seen these cool answers,:

• SO 1
• SO 2
• SO 3
• SO 4

...but still struggling.

Thanks in advance!

Answer 1

The below query will help on this requirement. But you need to make sure the A column FULLTEXT Indexed.

ALTER TABLE `test` ADD FULLTEXT(`A`);

SELECT * FROM `test` WHERE (match(A) against('Cinema, Entertainment' IN BOOLEAN MODE));

Answer 2

Use mysql FIND_IN_SET function to get your desired result.

select * from `test` where find_in_set('Cinema',`A`) OR find_in_set('Entertainment',`A`);

Demo