Prefix best match MySQL

Question

That's my scenario. I have 4 tables: records, providers, routing_domain, domains.

• domains: id, name (something like 'example.com')
• providers: id, name (something like 'TLC')
• records: phone_number (varchar), provider_id (foreign key to providers)
• routing_domain: provider_id (foreign key to providers), domain_id (foreign key to domains) and prefix (varchar).

Example of tables:

mysql> select id,name from domains;
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | e164.arpa             |
|  3 | example.com           |
|  0 | localhost.localdomain |
|  4 | luigi.it              |
|  2 | tim.it                |
+----+-----------------------+

mysql> select id,name from providers where id in (9,10);
+----+----------+
| id | name     |
+----+----------+
|  9 | TIM      |
| 10 | VODAFONE |
+----+----------+    

mysql> select * from routing_domain;
+----+--------+-----------+-------------+
| id | prefix | domain_id | provider_id |
+----+--------+-----------+-------------+
|  3 | 3932   |         4 |           9 |
|  1 | 39320  |         2 |           9 |
|  2 | 39321  |         3 |          10 |
+----+--------+-----------+-------------+

Now,

• given a phone_number '39320xxxxxxx' with provider_id 9, i need to get domain_id=2;
• given a phone_number '39321xxxxxxx' with provider_id 9, i need to get domain_id=4;

So, given a certain phone_number '3932xxxxxxxx' with provider_id=9, i need to do some bestmatch searching. Starting to search prefix with 6 chars, if not match, try with 5 chars and so on, until 3 chars (393).

I managed to get the correct domain from phone_number searching only from a prefix to 5 chars.

Something like:

select * FROM records r
left join routing_domain rd on rd.prefix like SUBSTRING(r.phone_number,1,5) and r.provider_id = rd.provider_id 
left join providers p on p.id = rd.provider_id
left join domains d on d.id = rd.domain_id 
where r.name = 'xxxxxxxxxxxx';

Any advices to do this bestmatch ? Thank you so much!

Update

I tried with this:

select * FROM records r
left join routing_domain rd on on r.phone_number like concat(rd.prefix, '%') and r.provider_id = rd.provider_id 
left join providers p on p.id = rd.provider_id
left join domains d on d.id = rd.domain_id 
where r.name = 'xxxxxxxxxxxx';

Now, if i search for '39325xxxxxxx', there is a match with prefix '3932', but if i search for '39320xxxxxxx', both prefixes will match and the search returns 2 rows.

Answer 1

One option is to have a sub-query that gives you the longest prefix matching provider_id and prefix. Something like this:

select domain_id from routing_domain
where
  provider_id = 9
  and '39321xxxxxxx' like concat(prefix, '%')
  and length(prefix) =
  (    select max(length(prefix))
       from routing_domain
       where 
         provider_id = 9
         and '39321xxxxxxx' like concat(prefix, '%')
  )

See my fiddle here.