Using LIKE "backwards" to find patterns that match an input

Suppose you want to get all rows in a database table where the value in a field (`pattern`) appears in a specific given string.

For example, suppose you have a data table that tells the computer what response to spit out based on whether certain words appear in the user's input:

row	pattern	response
1	hello	Hi.
2	mother	Tell me about your family.
3	flowers	I love all nature.

The user types in the string: Do you like flowers?. You want the computer to respond: I love all nature.

A simple and fast solution is this:

SELECT `response` FROM `table` WHERE 'Do you like flowers?' LIKE CONCAT('%',`pattern`,'%')

I know this is different from the way that you usually use the LIKE operator. The standard way to use LIKE is something along these lines:

SELECT `response` FROM `table` WHERE `pattern` LIKE '%low%'

This will return all rows where the characters in the field pattern include the text low. But what we want in the above example is exactly the opposite: we have a longer text string, and we want to return all of the rows where the (shorter) value in the field is included in that longer strong.

By reversing the normal order of things—putting the static text string on the left and the field name (concatenated with wildcards) on the right—we can achieve this opposite effect.

A number of variations can let you perform a number of styles of simple text match.

Find all rows where the word in pattern is the first word in the input sentence:

SELECT `response` FROM `table` WHERE 'hello, mother. would you like some flowers?' 
       LIKE CONCAT(`pattern`,'%')

(The above example only matches one row, because it only matches cases where the pattern is the start of the string.) Alternatively, you can simulate "greedy" pattern matching when you may get multiple results. For example, suppose you have this table:

row	pattern	response
1	app	Yes, but is it a KILLER app?
2	apple	It keeps the doctor away.
3	apple of my eye	That is so sweet!

You want the user string You are the apple of my eye! to only match the last item, not the first two. You can use this:

SELECT `response` FROM `table` WHERE 'you are the apple of my eye!' LIKE CONCAT('%',`pattern`,'%') 
ORDER BY LENGTH(`pattern`) DESC LIMIT 1

And so on. Keep in mind that when you use this method, you can even have the wildcard ('%') inside the values of the database table, as well! So you can have a value in the pattern column with things like 'big%dog' and the row will be returned if the static input string includes 'big black dog' or 'big angry dog' and so on.

The possibilities are endless. And it all begins with realizing that LIKE can be used in more than the usual way.