I have a table called customers in which i need to split the address field into four parts streetnumber streetname unittype and unit
so that given becomes
address street number streetname unittype unit
123 main unit 12 123 main unit 12
123 main #12 123 main # 12
123 main lot 12 123 main lot 12
123 main ste 12 123 main ste 12
123 main 123 main
all addresses that have some form of unit number are at the end of the address.
I need to also remove the transferred data from the orginal field.
I can seperate the streetnumer with no problem leaving the street name and unit number, but I am not sure how to process the array.
Please aid me in the best way possible to accomplish this feat.