Breaking up an address into chuncks

[carlblanchard]

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

[Andy]

without a delimiter, you are probably up the creek. unless that is, you can use the city name as a delimiter.

ex 5150 Mean Street Los Angeles CA ou812

you can use the indexof() function to find "los angeles"
then find CA and what you do is parse on either side.

Keep in mind that would only work if you had 1) a small amount of cities and states and 2) you were willing to type them in

by the way...who's idea was it to store them on your databse like that in the first place?

[carlblanchard]

basically i built a indexer than grabs details from certain websites, one of them being the full address, at the time of getting it all to work it was quicker for me to just store the address as a complete string, just to see if the program was do-able, now i know it is ive been refining it and i need to address the Address issue, i could have a <br> delimiter but i would still get a problem with knowing what line goes where ??? whats ya comments