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?
source to share
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.
source to share