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
