Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 5 Jul 2011 CPOL
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:


rowpatternresponse
1helloHi.
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:


rowpatternresponse
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`,'%') 
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.

License

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

Share

About the Author

GregStevens
Web Developer
United States United States
I'm just some guy.
Follow on   Twitter

Comments and Discussions

 
GeneralRe: Thanks for your help! PinmemberGregStevens5-Jul-11 17:35 
GeneralRe: If you can use a more general syntax that is shared among th... PinprotectorAspDotNetDev5-Jul-11 17:13 
GeneralRe: Newbie question: if I have a tip that is generically for SQL... PinmemberGregStevens5-Jul-11 12:51 
GeneralRe: Thank you very much for adding the MySQL tag to this tip! ... PinmemberGregStevens5-Jul-11 12:48 
GeneralRe: Interesting, I've never seen that done before. And I assumed... PinprotectorAspDotNetDev5-Jul-11 11:46 
GeneralReason for my vote of 5 Cool tip :) PinmemberShahar Eldad11-Jul-11 19:09 
GeneralYou are absolutely correct -- when initially submitting this... PinmemberGregStevens5-Jul-11 10:24 
GeneralYou hav eput "pattern" and "name" in single quotes. For one,... PinprotectorAspDotNetDev5-Jul-11 10:16 
GeneralRe: You are absolutely correct -- when initially submitting this... PinmemberGregStevens5-Jul-11 10:26 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 5 Jul 2011
Article Copyright 2011 by GregStevens
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid