Hi all, ok we have a database with millions of addresses all stored in just one nvarchar column...

ie. column name | value
Address | 99 Red Hill Drive Town County Post code
Address | 12 the street estate area town county post code

as you can see there are no delimiters to help filter this
we want to store the addresses in our format of

Column Names |

ADDRESS1 |99 RED HILL DRIVE
ADDRESS2 |
ADDRESS3 |
ADDRESS4 |
TOWN |TOWN
COUNTY |COUNTY
POSTCODE |POST CODE

OK so the massive question is how can we get these addresses into our required format.
I've been thinking about it and maybe we could work backwards, use a regular expression to determin the post code(thankfully every record has a post code)

whats your comments and views