title: How do I split text into multiple fields using Postgresql?
body: <p>I have a table with a column that needs to be split and inserted into a new table. Column's name is <code>location</code> and has data that could look like <code>Detroit, MI, USA;Chicago, IL, USA</code> or as simple as <code>USA</code>. </p>
  
  <p>Ultimately, I want to insert the data into a new dimension table that looks like:</p>
  
  <pre><code>City   | State | Country|
  Detroit  MI      USA
  Chicago  IL      USA
  NULL     NULL    USA
  </code></pre>
  
  <p>I came across the <code>string_to_array</code> function and am able to split the larger example (<code>Detroit, MI, USA; Chicago, IL, USA)</code> into 2 strings of <code>Detroit, MI, USA</code> and <code>Chicago, IL, USA</code>. </p>
  
  <p>Now I'm  stumped on how to split those strings again and then insert them. Since there are two strings separated by a comma, does using <code>string_to_array</code> again work? It doesn't seem to work in Sqlfiddle. </p>
  
  <p>Note: I'm using Sqlfiddle right now since I don't have access to my Redshift table at the moment.</p>
  
  <p>This is for Redshift, which unfortunately is still using PostGresql 8.0.2 and thus does not have the <code>unnest</code> function</p>