Going Postal – Validating UK Postcode format in PL/SQL

As a database programmer, addresses will crop up from time to time with the inevitability of death and taxes. Depending on the state of the database your working on at the time, it can feel like either or, in extreme cases, both.
This post is about the joy of UK postcodes and how you can validate that they are correctly formatted with…wait for it….regular expressions.
Before you run off screaming into the woods, I’d hasten to add that this can be achieved without overdue exertion of the top row of your keyboard.

First off, let’s look at what constitutes a valid format for a UK postcode. At this point, we need to refer to the ominously named BS7666.
It states that the following are valid formats (where A is a letter and N is a number) :

• AN NAA
• ANN NAA
• AAN NAA
• AANN NAA
• ANA NAA
• AANA NAA

…and also, just in case that sounds too much like your singing along to a song and have forgotten the words ( or possibly, are calling the name “Anna” whilst falling off a cliff), GIR 0AA.
What rules can we divine from this wisdom ?
Well, it seems that any valid format must :

• start with a letter
• must have at least one and up to 3 further characters before a space
• The space must be followed by a number
• There must then be at least a further two, but no more than three, alphanumeric characters
• must be a maximum of 8 charaters ( including the space)

Time to visit regular expression land :

[:alpha:] is any letter
[:alnum:] is any letter or number
{n} means match the preceeding charater set n times
{x,y} match the preceeding charter set at least x times but no more than y times
^ – anchor the expression to the start of the line

So, putting this all together :

CREATE OR REPLACE FUNCTION validate_postcode_format_fn( pa_pcode IN VARCHAR2)
    RETURN BOOLEAN AS
BEGIN
    IF LENGTH( pa_pcode) > 8 THEN
        RETURN FALSE;
    END IF;
    IF REGEXP_LIKE( pa_pcode, '^[[:alpha:]]{1}[[:alnum:]]{1,3} {1}[[:alnum:]]{3}')
    THEN
        RETURN TRUE;
    END IF;
    RETURN FALSE;
END;
/

And now for the test script …

set serveroutput on size 1000000
accept pcode prompt 'Enter Postcode : '
DECLARE
    l_pcode VARCHAR2(8) := UPPER('&pcode');
BEGIN
    IF validate_postcode_format_fn( l_pcode) THEN
        DBMS_OUTPUT.PUT_LINE(l_pcode||' is a valid postcode format');
    ELSE
        DBMS_OUTPUT.PUT_LINE( l_pcode||' is not a valid postcode format');
    END IF;
END;
/

Of course, it this doesn’t guarantee that what’s entered is a valid postcode ( try SIL LY1), but at least it does filter out the obviously incorrect stuff.

About these ads

Tags: Anti-Kyte, BS7666, regexp_like, UK Postcode