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
– Narayana J, Commented Mar 21, 2017 at 4:59
@VikasUmrao, thanks, but beats me. Database newbie that I am, not sure what full text search means... — Narayana J
– Narayana J, Commented Mar 21, 2017 at 5:00

Rafiqul Islam · Accepted Answer · 2017-03-21 06:07:55Z

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, 2017 at 6:07

Rafiqul Islam

1,6561 gold badge14 silver badges25 bronze badges

answered Mar 21, 2017 at 5:01

Naga

2,1683 gold badges18 silver badges21 bronze badges

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

1 Comment

Narayana J Over a year ago

Thanks @Naga. SELECT * FROM test WHERE match(A) against('Cinema, Entertainment' IN BOOLEAN MODE) works.

Rafiqul Islam · Accepted Answer · 2017-03-21 05:37:43Z

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, 2017 at 5:37

answered Mar 21, 2017 at 5:24

Rafiqul Islam

1,6561 gold badge14 silver badges25 bronze badges

1 Comment

Narayana J Over a year ago

Thanks! But like I said, I do not want to tokenize the available string "Cinema, Entertainment". However, a new learning this.

Collectives™ on Stack Overflow

MySQL string 'contains' functionality

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related