Thanks for your very detailed response Mark. Most helpful.
On Wed, Apr 25, 2012 at 10:14 AM, Mark Goodge <mark@stripped> wrote:
> On 24/04/2012 17:24, Tompkins Neil 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
> No, you don't. In this example you have W1U as one outbound code and W13
> as the other.
> W1U postcodes are not a subset of W1 postcodes, any more than IP27
> postcodes are a subset of IP2 postcodes. The fact that in W1U the district
> segment is in the form of NA rather than NN doesn't change the fact that
> it's an indivisible two-character code.
> So I think the first question has to be, why do you want to get W1 as a
> particular substring from the postcode W1U 8JE?
> British postcodes have a structure which is easy for humans to understand,
> although (unfortunately) rather hard to parse automatically. Essentially,
> every full postcode contains four elements:
> Area code: one or two alpha characters, either A or AA
> District code: one or two alphanumeric characters the first of which is
> always numeric, either N, NN or NA
> Sector code: single numeric character, always N
> Walk code: two alpha characters, always AA
> It's customary, but not part of the formal specification, to insert
> whitespace between the District and Sector codes.
> So, given the postcode WC1H 8EJ, we have:
> Area: WC
> District: 1H
> Sector: 8
> Walk: EJ
> Taken together, the first two sections form the "outbound" part of the
> postcode, and the second two form the "inbound". (That is, the first two
> identify the destination sorting depot that the originating depot will send
> the post to, and the second two are used by the destination depot to make
> the actual delivery).
> The reason for mentioning this is that postcodes, having a wide range of
> possible formats, are not easy to handle with simple substring searches if
> you're trying to extract outbound codes from a full postcode. It can be
> done with regular expressions, but you have to be wary of assuming that the
> space between District and Sector will always be present as, particularly
> if you're getting data from user input, it might not be.
> In my own experience (which is quite extensive, as I've done a lot of work
> with systems, such as online retail, which use postcodes as a key part of
> the data), I've always found it simpler to pre-process the postcodes prior
> to inserting them into the database in order to ensure they have a
> consistent format (eg, inserting a space if none exists). That then makes
> it easy to select an outbound code, as you can use the space as a boundary.
> But if you want to be able to go further up the tree and select area codes
> (eg, distinguishing between EC, WC and W) then it's harder, as you have to
> account for the fact that some are two characters and some are only one.
> You can do it with a regular expression, taking everything prior to the
> first digit, but it's a lot easier in this case to extract the area code
> prior to inserting the data into the database and store the area code in a
> separate column.
> Sent from my ZX Spectrum HD
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql