From: Neil Tompkins Date: April 24 2012 6:16pm Subject: Re: Postal code searching List-Archive: http://lists.mysql.com/mysql/227243 Message-Id: MIME-Version: 1.0 (1.0) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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 wrote: > Please be more precise about the rules. In the US, "12345-6789" would bec= ome "12345". This would follow a different rule. >=20 > Is your rule "stop after the first digit"? That gets quite messy in SQL, a= nd would be better done in an application code. >=20 > See also > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_subs= tring-index >=20 > RLIKE can distinguish digits from letters, but won't help you isolate them= . >=20 >> -----Original Message----- >> From: Tompkins Neil [mailto:neil.tompkins@stripped] >> Sent: Tuesday, April 24, 2012 9:11 AM >> To: [MySQL] >> Subject: Postal code searching >>=20 >> Hi >>=20 >> I've a number of different postal codes in a system for example >>=20 >> WC1B 5JA >> WC1H 8EJ >> W1J 7BX >> W1H 7DL >> NW1 1NY >>=20 >> I can use like statements for example >>=20 >> SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me >>=20 >> W1J 7BX >> W1H 7DL >>=20 >> In addition I have a number of abbreviated postal codes like >>=20 >> W1 >> WC1 >> WC2 >> NW1 >>=20 >> 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 >>=20 >> Can I use any matching patterns ? >>=20 >> Thanks, >> Neil