title: SQL Server Split Varying Address length into specific columns
body: <p>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 </p>
  
  <pre><code>Address, line 1
  Address, line 2
  Address, line 3
  Address, line 4
  Address, line 5
  City
  County
  PostCode
  Country
  </code></pre>
  
  <p>Example data:</p>
  
  <pre><code>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
  </code></pre>