title: Split City, State, Zip from one column into 3 separate, also add dash to 9 digit zips
body: <p>I have a table column that contains City, State, and Zip.  I would like to split this into 3 separate </p>
  
  <p>I'm wondering if I'm going about this wrong.  Here is my attempt at extracting all 3.  </p>
  
  <pre><code>SELECT [City State Zip]
      ,CHARINDEX(',',[City State Zip]) AS [Comma location]
      ,SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip]),13) AS [State and Zip]
      ,SUBSTRING(SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip]),13),5,9)
  
  -- Below code attempts to add a dash to the 9 digit zip codes but appears to only be doing it to some of them
      CASE LEN(SUBSTRING(SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip]),13),5,9))
          WHEN 9
          THEN
              STUFF((SUBSTRING(SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip]),13),5,9)
          ELSE
              (SUBSTRING(SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip]),13),5,9)
          END AS Zip
  
      ,SUBSTRING([City State Zip],0,CHARINDEX(',',[City State Zip])) AS City
  
  -- This code for extracting the STATE is producing an error "Invalid length parameter passed to the SUBSTRING function"
      ,SUBSTRING([City State Zip],CHARINDEX(',',[City State Zip])+1,LEN([City State Zip])- (CHARINDEX(',',[City State Zip])+1 + 5))
  
  FROM dbo.foo
  </code></pre>
  
  <p>SO now how do I extract the State? Currently it fails with "Invalid length parameter passed to the SUBSTRING function"</p>
  
  <p>The City will always be followed by a comma, and the state will always be 2 digits.</p>
  
  <p>Sample data below:</p>
  
  <p>Georgetown, DE 19947</p>
  
  <p>Greenwood, DE 199502039</p>
  
  <p>Dover, DE 19901</p>
  
  <p>New Castle, DE 197205069</p>
  
  <p>Lewes, DE 199581984</p>
  
  <p>Newark, DE 197118734</p>
  
  <p>Smyrna, DE 19904</p>
  
  <p>Baltimore, MD 21020</p>
  
  <p>Dover, DE 19901</p>