TareKhoury February 2016

How to search a MySQL table for the most accurate matching

I have a table that contains a row of phone number prefixes and a row of price for each prefix.

Sample table:

   prefix  |  price
 ---------------------
|  21366   |   0.15   |
|  2010    |   0.1    |
|  213     |   0.13   |
 ---------------------

In the website, the user will be asked to insert a phone number (e.g. 21366123456), where i have to search the database table for the most accurate matching.

In this case, even though there is a prefix value of 213 in the table, i will be needing the result of 21366.

How can this be done using a simple MySQL query without looping recursively on the user input value?

Answers


Giorgos Betsos February 2016

Try this:

SELECT prefix, price
FROM mytable
ORDER BY LENGTH('21366123456') - LENGTH(REPLACE('21366123456', prefix, '')) DESC LIMIT 1

Demo here

Post Status

Asked in February 2016
Viewed 2,683 times
Voted 13
Answered 1 times

Search




Leave an answer