Validation: lookup against international dialling codes

I don’t normally post code samples, but this one might come in handy for somebody building a calling app. Given the following scenario, you’d do the following:

1. Get the number the user wants to call
2. Run it against a table with available dialling codes
3. Return a single matching record

You’d normally do this by forward / backward recursing through each character of the test number until you found the longest match and then return that. This would require a server-side loop with a db call on each iteration.

I found a much quicker way of doing this with the following sql query:

select *
from `{table_name}`
where {test_number} like(concat(`{codes_column}`,'%'))
order by `{codes_column}` desc
limit 1

It’s really quick to execute on a large table. Hopefully it comes in handy for someone looking for it.