>>>> 2012/04/25 10:14 +0100, Mark Goodge >>>>
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
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:
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.
It seems to me that sector & walk taken together always make up three characters;
therefore, blanks aside, the outbound part from a good postcode is
, and with REPLACE it is trivial to drop all blanks. If Neil Tompkins wanted only to get
the outbound part, that is enough. As for the area, if it is only one or twain characters
long, to get that this is enough:
LEFT(pc, IF(SUBSTR(pc, 2, 1) > '9', 2, 1))
. Extremely crude coding, but if the postcode is right, .... This much one can do within
an SQL function with no regular-expression handling --and MySQL s regular-expression
handling yields only 'yes' or 'no'-- , but, of course, if one wishes to verify that it is
right, that is another matter. Are there any rules for that, or is the best recourse to
get a file of good outbound codes from the post office?
As for the string-matching question, matching 'W1' and 'W13' against 'W13 0SU', one rule
to consider is that the longest match is the right one. This problem or like is often
given in SQL classes:
FROM pc JOIN shortpc ON LEFT(pc.c, CHAR_LENGTH(shortpc.c)) = shortpc.c
WHERE (SELECT MAX(CHAR_LENGTH(shortpc.c))
FROM pc AS a JOIN shortpc ON LEFT(a.c, CHAR_LENGTH(shortpc.c)) = shortpc.c
WHERE a.c = pc.c) = LENGTH(shortpc.c)
I hope this is not a class problem.
And after this I ask, is any outbound code a leading part of any other outbound code? If
not, this twist is not needed. I also consider it good design if not.
I was glad to see this lesson in British postcodes, something that I never pursued because
I had no need of it.