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
