42

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?

giliev
  • 2,938
  • 4
  • 27
  • 47
serg
  • 109,619
  • 77
  • 317
  • 330
  • 1
    I too am confused by the question. Off the top of my head, what about using a proper FullText search engine, e.g. Sphinx or Solr/Lucene? – Cody Caughlan Jan 23 '09 at 06:43
  • I think whats required here is something like this select name from user where name in (Search String) "John Smith and Peter Johnson are best friends". The output should be Johm Smith, Peter Johnson etc, provided they are in name. Basically a reverse FULL text search. – Dheer Jan 23 '09 at 06:46

3 Answers3

72

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, '%')
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Dheer. Oh? It seems to work for Joel. http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause – Zoredache Jan 23 '09 at 07:17
7
select name
from users
where instr('John Smith and Peter Johnson', name) > 0

I would rather use this method instead of:

select *
from users
WHERE  'John Smith and Peter Johnson' LIKE CONCAT('%', name ,'%')

because if there is any chance that the name could contain the % or _ character (eg. name='v%alue') then the above query would still return that record incorrectly. Also note that if your column could contain backslashes and/or "C escape characters" then they would also need to be escaped. This is all explained in MySQL String Comparison Functions. For example the below SQL would return 1:

SELECT 'val\%ue' LIKE CONCAT('%', 'al\\\\\%u' ,'%');

The single backslash needed to be escaped with \\\\ and the % character was escaped: \%.

joshweir
  • 5,427
  • 3
  • 39
  • 59
-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.

Stepan Stolyarov
  • 617
  • 6
  • 11