List:General Discussion« Previous MessageNext Message »
From:Grant Allen Date:April 24 2012 10:57pm
Subject:Re: Postal code searching
View as plain text  
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 <rjames@stripped> 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.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