title: How can I get a natural numeric sort order in Oracle?
body: <p>I have a column with a letter followed by either numbers or letters:</p>
  
  <pre><code>ID_Col
  ------
  S001
  S1001
  S090
  SV911
  SV800
  Sfoofo
  Szap
  Sbart
  </code></pre>
  
  <p>How can I order it naturally with the <strong>numbers first</strong> (ASC) then the letters alphabetically? If it starts with <code>S</code> and the remaining characters are numbers, sort by the numbers. Else, sort by the letter. So <code>SV911</code>should be sorted at the end with the letters since it also contains a <code>V</code>. E.g.</p>
  
  <pre><code>ID_Col
  ------
  S001
  S090
  S1001
  Sbart
  Sfoofo
  SV800
  SV911
  Szap
  </code></pre>
  
  <p>I see this <a href="https://stackoverflow.com/questions/219982/oracle-how-can-i-implement-a-natural-order-by-in-a-sql-query">solution</a> uses regex combined with the <code>TO_NUMBER</code> function, but since I also have entries with no numbers this doesn't seem to work for me. I tried the expression:</p>
  
  <pre><code>ORDER BY 
      TO_NUMBER(REGEXP_SUBSTR(ID_Col, '^S\d+$')), 
      ID_Col 
  
      /* gives ORA-01722: invalid number */
  </code></pre>