如果是数字则修剪前导零，如果是字母数字则不修剪零 [英] Trim leading zeroes if it is numeric and not trim zeroes if it is alphanumeric

问题描述

在一列中，有以0"开头的数字和字母数字值.如果是数字，如何修剪前导零，如果在 Oracle 中是字母数字，则不应修剪零.

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.

我需要在WHERE 条件中使用它.

例如

000012345 应该是 12345.012321 应该是 12321.00012JY12 应该是 00012JY12.

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

这是我试过的:

    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+)?$'))

推荐答案

只要有可能(在这种情况下)，使用标准字符串函数，例如 SUBSTR、INSTR、TRANSLATE 等，而不是正则表达式函数.正则表达式更强大，但也更耗时(正是因为这个原因)，所以它们应该只在真正需要时使用.

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.

如果列名是str，则:

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

TRANSLATE 会将 z 转换为自身，将所有数字转换为 NULL，将所有其他字符转换为自身.(唉，需要 z 或一些非数字字符.)

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.)

当且仅当 TRANSLATE 的结果为 NULL 时，输入为全数字.

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

演示:

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

这篇关于如果是数字则修剪前导零，如果是字母数字则不修剪零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！