I have a simple select query which draws in data to be used for a reconciliation with another group of data which I will be matching based on First Name, Last Name, Telephone Number etc.
An additional field I could use to match records is ADDRESS. Obviously, the formatting and styling of something as cumbersome as an address is rarely likely to match so I am just looking to draw in the very first word of the address. It's not much but it's something.
So both '16, Beaufort Street' and '16 Beaufort St' should both yield '16'.
How do I code this in SQL?
SELECT FIRST, LAST, TEL, xxxxx AS FirstWordOfAddress FROM CONTACTS
In the pseudocode 'xxxxx' is a formula to grab the first part of an address before a space or comma.
In Excel for example I use the formula:
(where D2 is the address)
The formula basically says:
- look for the position of the first space in the address (and if there isn't one, be 200)
- look for the position of the first comma in the address (and if there isn't one, be 200)
- take the minimum of these two values
- take the left of the address one short of that value
Is this at all doable with SQL?