MySQL: What is a reverse version of LIKE?

Question

LIKE operator in MySql is used to find rows that contain our query text, for example:

select name from user where name like "%john%"

which will return "John Smith", "Peter Johnson" etc.

What if I need the opposite - to find rows that are CONTAINED in our query text? For example I give it "John Smith and Peter Johnson are best friends" and want it to find me all names from the table that could be found in this string.

How to do that?

Answer 1

I'm a little confused by the question, but it sounds like a typical LIKE OR/AND, LIKE OR/AND type of comparison.

Care to suggest a couple example rows of data to illustrate this a little better?

Answer 2

This is a text indexing problem. If I get it right, your task is to find all references to people in a free-form text, and has nothing to do with LIKE or NOT LIKE.

In a general form, the database table against which you're looking for references acts as a dictionary to some text-indexing algorithm. Build an index on input string using this dictionary and hopefully you will get some matches.

Answer 3

Here's a way you can achieve what you describe:

SELECT name FROM user 
WHERE 'John Smith and Peter Johnson are best friends' LIKE
  CONCAT('%', name, '%')