That still leaves the question, what are the actual rules/business logic by which you want to group things and get their "abbreviations"? Are you adhering to Royal Mail/Post Office residual selection/direct selection rules, or do you have your own scheme? It seems like the latter ... the RM rules wouldn't abbreviate WC1H to WC1.
Have you tried REGEX matching based on your rules? Fuzzy :-) On 24/04/2012 14:16, Neil Tompkins wrote: > At the moment im concentrating on london postal codes but future would be us > zip codes too > > On 24 Apr 2012, at 18:09, Rick James <rja...@yahoo-inc.com> wrote: > >> Please be more precise about the rules. In the US, "12345-6789" would >> become "12345". This would follow a different rule. >> >> Is your rule "stop after the first digit"? That gets quite messy in SQL, >> and would be better done in an application code. >> >> See also >> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index >> >> RLIKE can distinguish digits from letters, but won't help you isolate them. >> >>> -----Original Message----- >>> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] >>> Sent: Tuesday, April 24, 2012 9:11 AM >>> To: [MySQL] >>> Subject: Postal code searching >>> >>> Hi >>> >>> I've a number of different postal codes in a system for example >>> >>> WC1B 5JA >>> WC1H 8EJ >>> W1J 7BX >>> W1H 7DL >>> NW1 1NY >>> >>> I can use like statements for example >>> >>> SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me >>> >>> W1J 7BX >>> W1H 7DL >>> >>> In addition I have a number of abbreviated postal codes like >>> >>> W1 >>> WC1 >>> WC2 >>> NW1 >>> >>> Now, if I know the postal code W1J 7BX what is the best way using a >>> MySQL query to get the abbreviated postal codes W1. Same if I have the >>> postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1 >>> >>> Can I use any matching patterns ? >>> >>> Thanks, >>> Neil > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
