Hi there,
I am working with the following database and i am trying to separate the postcode from the rest of the the address.
I know i could separate the columns by using 'London' as a delimiter and work from there.
From a skills development point of view I am wondering if it is possible to split it using a right trim using the second space, between London and the first letter of the postcode.
Also not all the postcodes are the same length in the data set, so i cant split on a certain character.
Any help would be appreciated greatly.
David
Solved! Go to Solution.
The moderators moved your post while I was writing to you. The bottom line is that I prefer a Regular Expression that looks at the possible values and populates the postal code based upon known PC formats (wikipedia):
The format is as follows, where A signifies a letter and 9 a digit:
Format | Coverage | Example |
---|---|---|
AA9A 9AA | WC postcode area; EC1–EC4, NW1W, SE1P, SW1 | EC1A 1BB |
A9A 9AA | E1W, N1C, N1P | W1A 0AX |
A9 9AA | B, E, G, L, M, N, S, W | M1 1AE |
A99 9AA | B33 8TH | |
AA9 9AA | All other postcodes | CR2 6XH |
AA99 9AA | DN55 1PT |
The code looks like this as a regular expression:
REGEX_Replace([Address], ".*(\w{2}\d\w\s\d\w{2}|\w\d\w\s\d\w{2}|\w\d\s\d\w{2}|\w{2}\d\s\d\w{2}|\w{2}\d{2}\s\d\w{2})", '$1')
The expression has 5 OR conditions, each separated by a pipe (|) character.
I have included that in the attached workflow. Here is a picture of some alternative methods, each having it's own merits and cautions.
Cheers,
Mark
@MarqueeCrew
Thanks for the help! Appreciate it.
Best
David