Separating rows from the right on specific character

Question

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

MarqueeCrew · Accepted Answer

@David92,

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):

Validation[edit]

The format is as follows, where A signifies a letter and 9 a digit:

FormatCoverageExampleAA9A 9AAWC postcode area; EC1–EC4, NW1W, SE1P, SW1EC1A 1BBA9A 9AAE1W, N1C, N1PW1A 0AXA9 9AAB, E, G, L, M, N, S, WM1 1AEA99 9AAB33 8THAA9 9AAAll other postcodesCR2 6XHAA99 9AADN55 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

David92 · Accepted Answer

@MarqueeCrew

Thanks for the help!  Appreciate it.

Best

David