List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 24 2012 5:09pm
Subject:RE: Postal code searching
View as plain text  
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.tompkins@stripped]
> 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
Thread
Postal code searchingTompkins Neil24 Apr
  • Re: Postal code searchingGary Smith24 Apr
    • Re: Postal code searchingGary Smith24 Apr
      • Re: Postal code searchingTompkins Neil24 Apr
        • Re: Postal code searchingGary Smith24 Apr
        • Re: Postal code searchingLars Nilsson24 Apr
        • Re: Postal code searchingMark Goodge25 Apr
          • Re: Postal code searchingAndrew Moore25 Apr
          • Re: Postal code searchingTompkins Neil25 Apr
          • Re: Postal code searchinghsv25 Apr
          • Re: Postal code searchinghsv25 Apr
  • RE: Postal code searchingRick James24 Apr
    • Re: Postal code searchingNeil Tompkins24 Apr
      • Re: Postal code searchingGrant Allen24 Apr