title: "Safe" TO_NUMBER()
body: <pre><code>SELECT TO_NUMBER('*') FROM DUAL
  </code></pre>
  
  <p>This obviously gives me an exception:</p>
  
  <blockquote>
    <p>ORA-01722: invalid number</p>
  </blockquote>
  
  <p>Is there a way to "skip" it and get <code>0</code> or <code>NULL</code> instead?</p>
  
  <p>The whole issue: I have <code>NVARCHAR2</code> field, which contains numbers and not almost ;-) (like <code>*</code>) and I need to select the biggest number from the column.</p>
  
  <p>Yes, I know it is a terrible design, but this is what I need now... :-S</p>
  
  <p><strong>UPD</strong>:</p>
  
  <p>For myself I've solved this issue with</p>
  
  <pre><code>COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)
  </code></pre>