title: Extracting alpha and numeric parts from a column
body: <p>I have a table <code>tab1</code> with a column <code>col1</code> that has compound alpha-then-numeric values, like this:</p>
  
  <pre><code>abc123
  xy45
  def6
  z9
  </code></pre>
  
  <p>I need to extract the values as separate columns in a query, with the numeric part in a column of integer datatype.</p>
  
  <p>If the two values had a consistent start and end positions, the job could be done with <code>substring()</code>, as you can see the start of the numeric part varies.</p>
  
  <p>Is there an elegant way to tackle this, or must it be done with a series of unions of each possible start point using a regex match to separate the cases, or rolled up in a huge case statement?</p>