Trim leading zeroes if it is numeric and not trim zeroes if it is alphanumeric

Question

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.

I need to use it in WHERE Condition.

Ex.

000012345 should be 12345. 012321 should be 12321. 00012JY12 should be 00012JY12.

This is what I tried:

    SELECT COUNT(*)
    FROM <TABLE 1> ONN, <TABLE 2> SV
   WHERE SV.CSA_SHP_VISIT_STG_SEQ_ID=ONN.CSA_SHOP_VIST_SEQ_ID
    AND EXISTS (SELECT '1' FROM  <TABLE 3> 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+)?$'))

Answer 1

Whenever possible (in this case it is), use standard string functions, such as SUBSTR, INSTR, TRANSLATE, etc. instead of regular expression functions. Regular expressions are much more powerful, but also much more time consuming (precisely for that reason), so they should be used only when really needed.

If the column name is str, then:

case when translate(str, 'z0123456789', 'z') is null
     then ltrim(str, '0')
     else str                      end

TRANSLATE will translate z to itself, all the digits to NULL, and all other characters to themselves. (Alas, the z, or SOME non-digit character, is needed.)

The input is all-digits if and only if the result of TRANSLATE is NULL.

Demo:

select str, case when translate(str, 'z0123456789', 'z') is null
                 then ltrim(str, '0')
                 else str
            end  as new_str
from
(
  select '000012345' as str from dual union all
  select '012321'    as str from dual union all
  select '00012JY12' as str from dual
);

STR       NEW_STR 
--------- ---------
000012345 12345    
012321    12321    
00012JY12 00012JY12