SELECT MySQL field that contains a substring

Question

Using LIKE is very common in MySQL. We use it like this: WHERE field LIKE '%substring%'. Where we have a substring and field has full string. But what I need is something opposite. I have substrings in field. So, I want that row which contains a substring of my string. Suppose the table is:

----+-------------------
 id | keyword
----+-------------------
  1 | admission
----+-------------------
  2 | head of the dept
----+-------------------

and I have a string from user: Tell me about admission info. I need such a MySQL query that returns admission as this is a substring of user string. Something like:

SELECT keyword FROM table WHERE (keyword is a substring of 'Tell me about admission info')

thanks in advance.

Answer 1

You re looking for the LIKE operator

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

Something like

SELECT  keyword 
FROM    table 
WHERE   ('Tell me about admission info' LIKE CONCAT('%', keyword, '%'))

SQL Fiddle DEMO

Answer 2

This work fine, using REGEXP:

SELECT  keyword 
FROM    table 
WHERE   'Tell me about admission info' REGEXP keyword;

But this work only if keyword don't contain Regular expression's escapes...

I.e. This will work fine while keyword contain only letters, numbers, spaces and so on.

Answer 3

Try something like this:

SELECT CASE WHEN 'Tell me about admission info' 
   LIKE CONCAT('%',`keyword`,'%')
   THEN `keyword` else null END as `keyword`

FROM    table1

WHERE CASE WHEN 'Tell me about admission info' 
  LIKE CONCAT('%',`keyword`,'%')
  THEN `keyword` else null END is not null;

SQL Fiddle..