Clever Geek Handbook

SQL Query for closest match at the beginning of a string

Currently Mysql and PHP are used.

We are looking for a query that will take a number and find a matching cabinet to start dialing numbers, for example, I have a number 019235678910, 026725678910, 026825678910

, and my table looks like this.

Table - Destintation

Name     Number
Watford  01923
Oxford   026
Romford  026
Crawford 0267
Topford  02672

So when I get through 019235678910

, the result will be Watford

, 026725678910

will be Topford

, and 026825678910

will be Oxford

and Romford

.

I'm also not sure if MYSQL can do this directly or work with PHP?

+3


source to share


2 answers


Here's one way to get all of them:

select d.*
from Destination d join
     (select length(Number) as maxlen, number
      from destination d
      where YOURVALUE like concat(Number, '%')
      order by maxlen desc
      limit 1
     ) dsum
     on d.Number = dsum.Number


Since you are looking for initial sequences, there is only one maximum number match (hence works limit 1

).

By the way, the field called number

is clearly a character field. Personally, I find it bad practice to call a character's field a "number" - something called cognitive dissonance.

+2


source 


SELECT Name, Number
FROM Destintation
WHERE LEFT('026725678910', LENGTH(Number)) = Number

or maybe



WHERE '026725678910' LIKE CONCAT(Number, '%')
0


source






More articles:


All Articles