homepage Welcome to WebmasterWorld Guest from 54.196.63.93
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Ordering results by length of matching substring
Looking for a simple function to achive this
R1chard

10+ Year Member



 
Msg#: 3620889 posted 2:25 pm on Apr 7, 2008 (gmt 0)

Hi everyone,

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()
LIMIT 4;

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.

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved