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.
Mark
--
Sent from my ZX Spectrum HD
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
