I am trying to split a varying string Address into a specific Address format columns. I have tried using REPLACE,PARSENAME,SUBSTRING,LEFT,RIGHT but it's not getting me anywhere. Here is the format of the address field, as you can see there can be anything from 4 columns to 8 that I want to put into
Address, line 1
Address, line 2
Address, line 3
Address, line 4
Address, line 5
City
County
PostCode
Country
Example data:
LASHAM AIRFIELD,LASHAM,GU34 5SP,Alton,Hampshire,United Kingdom
Pinners Hall,105-108 Old Broad Street,EC2N 1ER,London,United Kingdom
Allenby Laboratories,Wigan Road,Westhoughton,BL5 2AL,Bolton,Lancashire,United Kingdom
Old Church House,Sandy Lane,Crawley Down,RH10 4HS,Crawley,West Sussex,United Kingdom
Woodgate Business Park,1 Bell Heath Way,B32 3BZ,Birmingham,West Midlands,United Kingdom
5 Tillingbourne Court,The Dorking Business Park,Station Road,RH4 1HJ,Dorking,Surrey,United Kingdom
93 Fleet Road,GU51 3PJ,Fleet,Hampshire,United Kingdom