Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012

    Unanswered: How to extract a postcode from address field

    Hello, Here's a tricky one for you...

    I have a field titled PICKUP and another DROPOFF, both these fields are addresses, which may or may not include postcodes (notice plural, as there may be multiple addersses in each of the cells).

    1a High St, L16 7PA ----> 25 London Road, E14 2AA, + CH1 3PF

    I've run a simple query to list all Pickup addresses and another to list all dropoff addresses.

    Is there anyway I can extract each of the postcodes and insert into additional fields?

    I'm not actually expecting this to be possible with my average DB experience but would prefer for someone to tell me so... then I might stop trying!! lol

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    (virtually) anything is possible
    UK postcodes are in the format
    XX99 9XX
    however the XX99 can be X9 or any combination, the trailing (minor) component is alwasy X99

    so based on the sample you have given
    you can easily find the pickup postcode, ASSUMING that it always followed by "---->"

    I think you will probably need to write a function to do this, although with some whizzo SQL you maybe able to get it in one hit

    the real fix is to store your address dat properly in a normalised database. you cna alwasy go back to this compressed format at any time in reports or forms, but it shouldn't be stored as such.

    frankly having two postcodes in the the drop off 'address' is daft, its not logical it doesn't represent the real world. A UK postcode identifies a specific geographical address (or small group of address). so to store 2 postcodes as a single 'drop off' address indicates your data model is flawed.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    hi Healdem, thank you for responding, your help is appreciated.

    firstly the "----> "is not part of the cell... it was to simply distinguish to the forum reader the 2 fields used.

    secondly, the way the DB started was there was always 1 pick up and 1 drop off point... but this has since developed into multiple addresses, and each record needed to contain such information without too much work involving multiple records for the same 'job'... as this would confuse during invoicing etc.

    Is there an expression which extracts a postcode from a text field?

    (I have average experience in building databases and writing expressions... I'm not familiar with SQL codes - I'm self taught)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts