Mar. 10 2011
0

Reverse pattern matching in MySQL

Say, for instance, you have a table of patterns (in my case: billing rates by telephone prefix) and you need to compare a string (in my case: a telephone number) against the rates. Which should be returned is a single pattern which best matches the string so more specific patterns would be allowed.

Here’s my database table (called rates):

+-----------------------------------------------------------------+
|  id  |       code     |         description          |   rate   |
+-----------------------------------------------------------------+
|   1  |       93       |            Test 1            |  0.40    |
|   2  |      9356      |            Test 2            |  0.50    |
|   3  |      93567     |            Test 3            |  0.60    |
+-----------------------------------------------------------------+

I want to compare the string 935679035 to it pull out the best matching code.

SELECT * FROM rates WHERE 93635214542 LIKE CONCAT(code, '%') ORDER BY LENGTH(code) DESC LIMIT 1;
+-----------------------------------------------------------------+
|  id  |       code     |         description          |   rate   |
+-----------------------------------------------------------------+
|   3  |      93567     |            Test 3            |  0.60    |
+-----------------------------------------------------------------+

That query does a little black magic by making the comparison string the haystack (what we’re searching through) and the code–with a little help formatting the pattern using CONCAT()–the needle (what we’re searching for). Then, it orders everything from greatest to least by length of string, since we know the better matching ones will always be longer. Finally, it returns just one row.

The % is a pattern matching symbol which is part of the LIKE operator syntax. % matches zero or more characters and _ matches exactly one character. You can use CONCAT() to string in those symbols.

If you need more power than LIKE and know regular expressions, try your hand at using the REGEXP operator.

Leave a Reply

Previous post:

Next post:

Last.fm interface from fmTuner (modified by me).

Twitter interface from HL Twitter.

Dedicated to my mom, Sharon. May she rest peacefully.
Love you always. (March 23, 1965 - November 12, 2011)

Unsupported Browser!

My site is designed for and currently displays best with modern browsers (and it looks like you don't have one!).

Everything should still function properly, but you won't be able to see all the fancy effects, text, or the way I intended my design to look.

Consider trying the latest version of either Firefox or Chrome... I think you'll like it!

Logan Bibby

P.S.: You can continue viewing my site by clicking the "close" link and you'll never see this again. :)