1

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+)?$'))
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Selvathalapathy S
  • 27
  • 5
  • Don't know exactly what you're doing, but this would be something better handled at the presentation level (whatever app or report you're using the data) if possible. – Jim Jan 29 '18 at 17:11

4 Answers4

7

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
  • You are right; I thought of `TRANSLATE`, but then decided for `REGEXP_REPLACE`, but the latter really takes 1.5 times as long with a large table in my database. A pity that Oracle doesn't treat '' as simply empty in `TRANSLATE`. While I've come to appreaciate Oracle's VARCHAR2 '' is null approach, they did a bad job with `TRANSLATE` concerning this. – Thorsten Kettner Jan 29 '18 at 17:11
  • I need to use this in where condition like ** SELECT COUNT(*) FROM ONN, SV WHERE SV.CSA_SHP_VISIT_STG_SEQ_ID=ONN.CSA_SHOP_VIST_SEQ_ID AND EXISTS (SELECT '1' FROM TMP WHERE TRIM(SV.WORK_ORDER_NUM) = TRIM(TMP.WORK_ORDER_NUM) AND PLANT IN ('EWL') AND (case when translate(TRIM(ONN.INSTLD_PART), 'z0123456789', 'z') is null then ltrim(TRIM(ONN.INSTLD_PART), '0') else TRIM(ONN.INSTLD_PART) end)=TRIM(TMP.INSTLD_PART) ); **. I tried this but its taking more time. – Selvathalapathy S Jan 29 '18 at 18:52
  • @SelvathalapathyS - **and... ??** –  Jan 29 '18 at 19:43
3

Use regexp_replace(col, '^0+([[:digit:]]+)$', '\1').

This replaces strings that only consist of leading zeros and trailing digits with the trailing digits alone, thus removing the zeros.

Sample query:

select col, regexp_replace(col, '^0+([[:digit:]]+)$', '\1') as newvalue
from
(
  select '000012345' as col from dual
  union all
  select '012321' as col from dual
  union all
  select '00012JY12' as col from dual
);

Result:

COL       | NEWVALUE
----------+----------
000012345 | 12345
012321    | 12321
00012JY12 | 00012JY12
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

If you are using Oracle 12.2, you could use the error handling of the CAST expression.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CAST.html#GUID-5A70235E-1209-4281-8521-B94497AAEF75

Along that:

CASE WHEN CAST(<expression> AS NUMERIC DEFAULT NULL ON CONVERSION ERROR) IS NULL
     THEN <expression>
     ELSE TRIM(LEADING '0' FROM <expression>)
 END

Replace <expression> with your column name (or whatever). I'm using null as magic value in the default null on conversion error clause but this does no harm as null input will then just match the THEN clause and pass the null through.

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
0

You can create a function that check if it is numeric or not, see this link for the function sample,

check if "it's a number" function in Oracle

You can TRIM the zeros of numeric value just by adding 0 to it,

Sample code:

--get  the IS_NUMERIC function from the link 

SELECT DECODE(IS_NUMERIC(col1), 'Y', col1+0, 'N', col1) 
  FROM your_table;
eifla001
  • 1,137
  • 8
  • 8