Click here to Skip to main content
13,344,508 members (58,529 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


6 bookmarked
Posted 5 Jul 2011

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

, 5 Jul 2011
Rate this:
Please Sign up or sign in to vote.
You can use the SQL LIKE operator in unconventional ways to do pattern matching.

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:

2motherTell me about your family.
3flowersI 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:

1appYes, but is it a KILLER app?
2appleIt keeps the doctor away.
3apple of my eyeThat 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`,'%') 

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
United States United States
I'm just some guy.

You may also be interested in...


Comments and Discussions

GeneralRe: Thanks for your help! Pin
GregStevens5-Jul-11 18:35
memberGregStevens5-Jul-11 18:35 
GeneralRe: If you can use a more general syntax that is shared among th... Pin
AspDotNetDev5-Jul-11 18:13
protectorAspDotNetDev5-Jul-11 18:13 
GeneralRe: Newbie question: if I have a tip that is generically for SQL... Pin
GregStevens5-Jul-11 13:51
memberGregStevens5-Jul-11 13:51 
GeneralRe: Thank you very much for adding the MySQL tag to this tip! ... Pin
GregStevens5-Jul-11 13:48
memberGregStevens5-Jul-11 13:48 
GeneralRe: Interesting, I've never seen that done before. And I assumed... Pin
AspDotNetDev5-Jul-11 12:46
protectorAspDotNetDev5-Jul-11 12:46 
GeneralReason for my vote of 5 Cool tip :) Pin
Shahar Eldad11-Jul-11 20:09
memberShahar Eldad11-Jul-11 20:09 
GeneralYou are absolutely correct -- when initially submitting this... Pin
GregStevens5-Jul-11 11:24
memberGregStevens5-Jul-11 11:24 
GeneralYou hav eput "pattern" and "name" in single quotes. For one,... Pin
AspDotNetDev5-Jul-11 11:16
protectorAspDotNetDev5-Jul-11 11:16 
You hav eput "pattern" and "name" in single quotes. For one, I do not see where you are getting "name". Secondly, you do not want to search for the string literal "pattern", you want to search for what is in the field "pattern".
GeneralRe: You are absolutely correct -- when initially submitting this... Pin
GregStevens5-Jul-11 11:26
memberGregStevens5-Jul-11 11:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 5 Jul 2011
Article Copyright 2011 by GregStevens
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid