title: Finding non-numeric values in varchar column
body: <p><strong>Requirement :</strong> </p>
  
  <p>Generic query/function to check if the value provided in a varchar column in a table is actually a number &amp; the precision does not exceed the allowed precision.</p>
  
  <p><strong>Available values:</strong></p>
  
  <p>Table_Name, Column_Name, Allowed Precision, Allowed Scale</p>
  
  <p>General advise would be to create a function &amp; use to_number() to validate the value however it won't validate the allowed length (precision-scale).</p>
  
  <p><strong>My solution:</strong></p>
  
  <p>Validate Number using Regexp <code>NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$')</code></p>
  
  <p>Validate Length of left component (before decimal) (I have no idea what's its actually called) because for scale, oracle automatically rounds off if required. As the actual column is varchar i will use substr, instr to find the component on the left of decimal point.</p>
  
  <p>As above Regexp allows number like 123...123124..55 I will also validate the number of decimal points. [If > 1 then error]</p>
  
  <p><strong>Query to find invalid number's:</strong></p>
  
  <pre><code>Select * From Table_Name 
  Where
  (NOT REGEXP_LIKE(COLUMN_NAME, '^-?[0-9.]+$')
  OR
  Function_To_Fetch_Left_Component(COLUMN_NAME) &gt; (Precision-Scale)
  /* Can use regexp_substr now but i already had a function for that */
  OR
  LENGTH(Column_Name) - LENGTH(REPLACE(Column_Name,'.','')) &gt; 1
  /* Can use regexp_count aswell*/)
  </code></pre>
  
  <hr>
  
  <p>I was happy &amp; satisfied with my solution until a column with only '.' value escaped my check and I saw the limitation of my checks. Although adding another check to validate this as well will solve my problem the <code>solution</code> as a whole looks very inefficient to me.</p>
  
  <p>I will really appreciate a better solution [in any way].</p>
  
  <p>Thanks in advance.</p>