What I'm after is a way to list items by the order of the maximum length of the string that matches with some fixed target string. For example, in the UK, a postal code can be up to 7 characters long.
Given one, say NW108TQ , I'd like to be able to list firstly all the items (if any) that match the full 7 characters (NW108TQ), and then those that only manage to match 6 characters (NW108T%), followed by 5 (NW108%), then 4, etc, down to those that only match 1 character (N%), and finally those that don't match any at all.
One way I've been doing this is as follows:
SELECT name,postcode FROM business
ORDER BY LEFT(postcode,4)='NW10' DESC,LEFT(postcode,3)='NW1' DESC,LEFT(postcode,2)='NW' DESC,LEFT(postcode,1)='N' DESC,RAND()
Obviously this is created by a PHP loop (I've only listed 4 terms above, but it will need to start from 7). But I guess there might be a simpler way that would shorten the code a bit (and presumably quicken things up as well). At the moment there are only about 20 records in the table, but ultimately I might end up with a couple of thousand, so matching substrings are likely to get a little bogged down, especially if I enhance this by using several joined tables later on.
I've not been able to find a built-in MySQL function for this, but I suspect there's some trick I can use. It would be great is there was something like MATCHLEN(a,b) that returned an integer...
Maybe I have to use regular expressions? Any help appreciated.