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
