Access SQL Query to split out the Postcode from memo field into new column

0
votes

I have an access database and within it I have a table that contains a memo field with multiple lines of text. This is used to store an address. I need to use regular expressions to find the postcode within that memo field (on whatever line it may be) and then put it into a new column.

This needs to be used in a select query to return a set of results accordingly.

Any help appreciated.

UPDATE: Sample Data

[Address Memo Column]:
10 The Street
A Town
A County
DE14 1DJ

I would need this to be split out into

[Address Memo column]:
10 The Street
A Town
A County

[PostCode column]:
DE14 1DJ

UPDATE:

The below should find the Postcode value but I dont know how to apply it to the Select statement and in addition split out the value into the new field once it finds the postcode.

([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)

UPDATE: Further info

The postcode is not always the final line in the memo fields as per below

[Address Memo Column]:
10 The Street
A Town
DE14 1DJ
Tel: 01234567891

11 The Street
DE14 1DJ
Tel: 01234567891
abcdefg

11 The Street
DE3 1DJ
Tel: 01234567891
abcdefg

2
sqlregexvbams-accessselect
Is this exactly how the data is stored or is each line of the address seperated by a charicter such as , or ^?? Also this post stackoverflow.com/questions/8844796/… does exactly what you are looking for it just splits the address more than just postcode – Dev N00B
It is stored as above (seperated with a carriage return/line break.... chr(13)). I have a function already that splits the separate lines of the memo field into new columns. If the post code is in line 2 it gets put into column 2, but I need to specifically look for the postcode value (could be line 2, could be line 4 like the example above) and put it into a specified field. – Brett

2 Answers

1
votes

This Example is base on a table like below:

enter image description here

SELECT Table2.id,
IIf([Table2.Address] Is Null,Null,(Right([Table2.Address],Len([Table2.Address])-InStrRev([Table2.Address],Chr(13))-1))) AS PostCode,
IIf([Table2.Address] Is Null,Null,(Left([Table2.Address],InStrRev([Table2.Address],Chr(13))-1))) AS RestofAddress  
FROM Table2;

Gives:

enter image description here

This Example does assume that the postcode is on the last line, and that all addresses have postcodes.

0
votes

What you really want to do is use the Instr function with wildcard characters instead of a fixed string. So I have written a custom function which does that:

Public Function InstrEx(str As String, strCompare As String, LenStrCompare As Integer) As Integer
    Dim i As Integer

    For i = 1 To Len(str)
        If Mid(str, i, LenStrCompare) Like strCompare Then
            InstrEx = i
            Exit Function
        End If
    Next
End Function

If you call this function in your SQL query you will get the position of the postcode.

Something like:

SELECT InStrEx([Address],"[A-Z][A-Z]## #[A-Z][A-Z]",8) AS Expr1
FROM Customers;

You can edit the wildcard character string, to include only valid characters for each position in the postcode.

Once you have the position, you just need to parse the Address field, using Mid and you have your postcode.