title: Trim leading zeroes if it is numeric and not trim zeroes if it is alphanumeric
body: <p>In a column, there are numeric and alphanumeric values starting with '0'.
  How to trim leading zeroes if it is numeric and should not trim zeroes if it is alphanumeric in Oracle.</p>
  
  <p>I need to use it in <strong>WHERE Condition</strong>.</p>
  
  <p>Ex.</p>
  
  <p><strong>000012345</strong> should be <strong>12345</strong>.
  <strong>012321</strong> should be <strong>12321</strong>.
  <strong>00012JY12</strong> should be <strong>00012JY12</strong>.</p>
  
  <p>This is what I tried:</p>
  
  <pre><code>    SELECT COUNT(*)
      FROM &lt;TABLE 1&gt; ONN, &lt;TABLE 2&gt; SV
     WHERE SV.CSA_SHP_VISIT_STG_SEQ_ID=ONN.CSA_SHOP_VIST_SEQ_ID
      AND EXISTS (SELECT '1' FROM  &lt;TABLE 3&gt; TMP 
      WHERE TRIM(SV.WORK_ORDER_NUM) = TRIM(TMP.WORK_ORDER_NUM)
      AND PLANT IN ('EMA')
      AND regexp_replace(TRIM(ONN.INSTLD_PART), '^0+([[:digit:]]+)$', 
     '\1')=TRIM(TMP.INSTLD_PART)  AND
    TRIM(ONN.INSTLD_PART_SERIAL_NUM)=TRIM(TMP.INSTLD_PART_SERIAL_NUM) AND      
      nvl(to_number(TRIM(ONN.INSTLD_PART_CSN)),0)=
      nvl(to_number(TRIM(TMP.INSTLD_PART_CSN)),0)
      and REGEXP_LIKE(tmp.INSTLD_PART_CSN, '^-?\d+(\.\d+)?$'))
  </code></pre>