title: Parse usable Street Address, City, State, Zip from a string
body: <p>Problem: I have an address field from an Access database which has been converted to SQL Server 2005. This field has everything all in one field. I need to parse out the address's individual sections into their appropriate fields in a normalized table. I need to do this for approximately 4,000 records, and it needs to be repeatable.</p>
  <p>Assumptions:</p>
  <ol>
  <li><p>Assume an address in the US (for now)</p>
  </li>
  <li><p>assume that the input string will sometimes contain an addressee (the person being addressed) and/or a second street address (i.e. Suite B)</p>
  </li>
  <li><p>states may be abbreviated</p>
  </li>
  <li><p>zip code could be standard 5 digits or zip+4</p>
  </li>
  <li><p>there are typos in some instances</p>
  </li>
  </ol>
  <p>UPDATE: In response to the questions posed, standards were not universally followed; I need need to store the individual values, not just geocode and errors means typo (corrected above)</p>
  <p>Sample Data:</p>
  <ul>
  <li><p>A. P. Croll &amp; Son  2299 Lewes-Georgetown Hwy, Georgetown, DE 19947</p>
  </li>
  <li><p>11522 Shawnee Road, Greenwood DE  19950</p>
  </li>
  <li><p>144 Kings Highway, S.W. Dover, DE  19901</p>
  </li>
  <li><p>Intergrated Const. Services 2 Penns Way Suite 405 New Castle, DE  19720</p>
  </li>
  <li><p>Humes Realty 33 Bridle Ridge Court, Lewes, DE  19958</p>
  </li>
  <li><p>Nichols Excavation 2742 Pulaski Hwy Newark, DE  19711</p>
  </li>
  <li><p>2284 Bryn Zion Road, Smyrna, DE  19904</p>
  </li>
  <li><p>VEI Dover Crossroads, LLC  1500 Serpentine Road, Suite 100 Baltimore MD  21</p>
  </li>
  <li><p>580 North Dupont Highway Dover, DE  19901</p>
  </li>
  <li><p>P.O. Box 778 Dover, DE  19903</p>
  </li>
  </ul>