title: SQL Server 2008 - separating Address field
body: <p>I have an address column that contains address, state and postcode. I would like to extract the address, suburb, state, and postcode into separate columns, how can a do this as the length of the address is variable, there is a ^ to separate the address and "other" details. The State can be 2 or 3 characters long and the postcode is always 4 characters long.</p>
  
  <pre><code>PostalAddress                            TO BE  Address   Suburb       State  Postcode
  28 Smith Avenue^MOOROOLBARK VIC 3138^    28 Smith Avenue  MOOROOLBARK  VIC    3138
  16 Farr Street^HEYFIELD VIC 3858^        16 Farr Street   HEYFIELD     VIC    3858
  17 Terry Road^LOWER PLENTY VIC 3093^     17 Terry Road    LOWER PLENTY VIC    3093  
  </code></pre>