List:General Discussion« Previous MessageNext Message »
From:Lars Nilsson Date:April 24 2012 4:52pm
Subject:Re: Postal code searching
View as plain text  
On Tue, Apr 24, 2012 at 12:24 PM, Tompkins Neil
<neil.tompkins@stripped> wrote:
> How about if I want to only return postal codes that are like W1U 8JE
> not W13 0SU.
>
> Because in this example I have W1 as the postal code and W13 is the other
> postal code

Perhaps something like following? Though, to be honest, I'm not sure
what rules you need to follow, and how to best resolve ambiguities, if
any should arise.

Both long_postal and short_postal tables contain a single varchar
column holding their respective codes.

select lp.code long_code, sp.code short_code
from long_postal lp, short_postal sp
where sp.code = substring(lp.code, 1, length(sp.code));

or, given a particular long postal code

select sp.code from short_postal sp where sp.code = substring('W1U
8JE', 1, length(sp.code))

Indices won't be of too much help for the short_postal table, I
imagine, for this kind of query..

Lars Nilsson
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