title: Oracle: to_number() combined with substr() and regexp_like() in WHERE clause
body: <p>I have a table with a column "description" which has the following values:</p>
  
  <ul>
  <li>OPTestMachine</li>
  <li>OPManualTesting</li>
  <li>OP1010</li>
  </ul>
  
  <p>So the select statement, to get the values, would just be </p>
  
  <pre><code>SELECT description
    FROM operation;
  </code></pre>
  
  <p>I want to extract the number "1010" (or any string which matches the substr() criterion) and convert the "found string" into an integer if possible.</p>
  
  <p>So I came up with this:</p>
  
  <pre><code>SELECT to_number(substr(description, 3, 4))
    FROM operation
   WHERE regexp_like(substr(description, 3, 4), '^\d+(\.\d+)?$', '')
  </code></pre>
  
  <p>The result is plain and simple: "1010"</p>
  
  <p>That works pretty well for me. </p>
  
  <p>Now the hard part for me: I want to use the substr()-result in the WHERE-clause</p>
  
  <p>Something like this:</p>
  
  <pre><code> SELECT to_number(substr(description, 3, 4))
     FROM operation
    WHERE regexp_like(substr(description, 3, 4), '^\d+(\.\d+)?$', '')
      AND substr(description, 3, 4) &lt; 2000;
  </code></pre>
  
  <p>When I do this I get the error "Invalid number". I guess it is because of the order how the server is parsing the select statement. </p>
  
  <p>If you could provide any help that'd be great!! </p>