Something came up in IRC today, I thought I found a pretty nice solution to it, thought I’d share it here. A guy has a table in his database that contains ‘prefixes’. It looks like this:
id |
number |
1 |
1 |
2 |
1800 |
3 |
18 |
4 |
180 |
5 |
1801 |
Given the phone number “18005551234”, he’d like to find the longest valid prefix. He had suggested to me that he was considering a stored procedure which loops, character-by-character, through the input looking for the longest match. Instead, I recommended he use ‘replace’ to trim the ‘prefixes’ off of the input, and let an ‘order by’ tell him which of those actually resulted in the shortest remaining string (and thus, was the longest match).
The query I ended up with goes something like this:
select id from prefixes order by length(replace('18005558355', number, '')) asc limit 1;
Given a better understanding of mysql’s ‘replace()’, we ended up using this instead – note the identities are renamed to reflect his actual sytem:
SELECT id, prefix from routes where locate(prefix, '18005558355') = 1 order by length(prefix) desc limit 1;
Is there a better way? Below the fold is a quick (postgresql) “dump” of the database in question so you can come up with your own proposals.