I’ve been involved with Carbon Guerrilla, a project to allow organisations to manage and track their CO2 emissions for some time now. Yesterday I was working on some code to allow people to compare emissions across locations by re-basing emissions from heating by degree day values for the locations in question. The degree day data is based on splitting the UK into eighteen different regions and I had a table of mappings from postcodes (actually prefixes of postcodes) to those regions, so to find the correct region for any given location I wanted to find the best match in the postcode table, where "best" means longest in this case.
Google came up with some very complex ways to find the longest match, apparently mostly relating to finding telephone number prefixes for billing (probably from asterisk), but I couldn’t believe there wouldn’t be a relatively simple way to do it. In the end I came up with this:
SELECT regionId FROM regionByPostcode WHERE <location-postcode> LIKE CONCAT( postcodePrefix, '%' ) ORDER BY LENGTH( postcodePrefix) LIMIT 1
I’ve not managed to break that in testing yet and I think I’m happy with it. I’m only matching against a few hundred postcodes so it’s never going to be too slow. I’m not sure about performance if it had to match against many millions though.