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,:

...but still struggling.

Thanks in advance!

@shmosel, thank you! You mean there should be no `CSV` in a single column? — Narayana J, Mar 21 '17 at 04:59
@VikasUmrao, thanks, but beats me. Database newbie that I am, not sure what _full text search_ means... — Narayana J, Mar 21 '17 at 05:00
Definitely not. You should use a `tag` table and another mapping table for each combination of `test.id, tag.id`. That will ensure data integrity and it will give you a lot more query flexibility. — shmosel, Mar 21 '17 at 05:03
You need to first define your column as full text index... and then give a try for the answer given by @Naga below — Vikas Umrao, Mar 21 '17 at 05:14

score 1 · Accepted Answer · edited Mar 21 '17 at 06:07

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

edited Mar 21 '17 at 06:07

Rafiqul Islam

1,636
1
12
25

answered Mar 21 '17 at 05:01

Naga

2,190
3
16
21

Thanks @Naga. `SELECT * FROM test WHERE match(A) against('Cinema, Entertainment' IN BOOLEAN MODE)` works. – Narayana J Mar 21 '17 at 05:21

Rafiqul Islam · Answer 2 · 2017-03-21T05:37:43.827

0

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

edited Mar 21 '17 at 05:37

answered Mar 21 '17 at 05:24

Rafiqul Islam

1,636
1
12
25

Thanks! But like I said, I do not want to _tokenize_ the available string "Cinema, Entertainment". However, a new learning this. – Narayana J Mar 21 '17 at 05:58

MySQL string 'contains' functionality

2 Answers2