0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jimbo
  • 1
  • 1
  • 1
    Tag the question with the database you are using. – Gordon Linoff Sep 07 '17 at 16:21
  • Do you have a fixed format? Otherwise what is your logic to split the string? – Juan Carlos Oropeza Sep 07 '17 at 16:23
  • Given the variable number of values, if possible you should try to establish recognition rules for city, county, postcode, and country. These rules may take the form of valid value lists or regular expressions. After you have matched everything that you can with these rules (assuming the rules are comprehensive), then everything preceding the first match can be considered to be address lines. – rd_nielsen Sep 07 '17 at 16:32
  • rd_Nielsen Yeah I don't think there is a quick fix for this scenario, as you say, specific fields aren't lined up so I will need to extract post codes then put them somewhere so on and so forth. – Jimbo Sep 08 '17 at 07:55

0 Answers0