Post Code improvement

stevenmckinnon

If a UK postcode is listed in format

AB123CD

AB12CD

A12CD

How can I strip this out to a normalised UK format ?

AB12 3CD

AB1 2CD

A1 2CD

Akshay Davis

@stevenmckinnon If you are using the Validate Addresses step within Data Studio, and the address is valid, it will format the postcode correctly.

However, if you want tor reformat a string in this way, you can simply concatenate the last three characters of the string with the first part and a space.

To extract the outcode (the last three characters of a postcode) we can simply take the substring of the last three characters.

For the incode, we do the same thing, but for all characters but the last three, and remove any trailing spaces if there are any.

We then concatenate these two together with a space in the middle.

Finally, you only want to apply this rule if the postcode meets the format expected for a UK postcode, so would add a check for that.

The example workflow and test data are below

postcodes.txt

PostcodeReformat.wfl

A note on postcode formats

"AB12 3CD" is not a valid UK postcode because the letter C never appears in the inward part of a postcode. This is also true of I, K, M, O and V.

Also, "A1" is not valid out code, "AB10" is the first valid out code. The example I will use here require valid postcode checks. The in build UK Postcode Format regex in Data Studio will not match these two examples, but if you wish to allow for these, you can use a simpler regex, which will match those invalid patterns.

^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$

stevenmckinnon

Thanks Akshay I will definitely be using that in the future.

With regards to the Address Validation step I didn't realise that it auto corrected the format so that is good to know however it does seem to be missing a lot. Any ideas ? I have checked some of these against https://www.royalmail.com/find-a-postcode and they seem to be valid.

Akshay Davis

The address validation step will try to validate the entire address, so for those blank entries it would have failed to definitively matched against that postcode. This can happen for a number of reasons, for example where the postcode and town or street don't match.

The last two columns added by default are the summary level to which the record matched. These are described in more detail here: https://www.edq.com/documentation/aperture-data-studio/validate-addresses-step/

If you want even more granular detail on why a record matched, failed to match or what corrections were made, you can turn on detailed reporting by selecting the detailed results option from the Advanced options in the step

This will produce more detailed responses. The FullMatchCode column contains the detailed match code from the underlying engine. To interpret the match codes, you need to refer to the Address Match Codes section of the Batch API Guide as well as the Dataset-Specific Information Bits section of the United Kingdom Data Guide