Обрезать ведущие нули, если он числовой, и не обрезать нули, если он буквенно-цифровой.

В столбце есть числовые и буквенно-цифровые значения, начинающиеся с «0». Как обрезать начальные нули, если он числовой, и не следует обрезать нули, если он буквенно-цифровой в Oracle.

Мне нужно использовать его в WHERE Condition.

Ex.

000012345 должно быть 12345. 012321 должно быть 12321. 00012JY12 должно быть 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+)?$'))

sql oracle leading-zero oracle11g character-trimming
person Selvathalapathy S    schedule 29.01.2018    source источник
comment
Не знаю точно, что вы делаете, но это было бы лучше обрабатывать на уровне презентации (независимо от того, какое приложение или отчет вы используете данные), если это возможно.   -  person Jim    schedule 29.01.2018

Ответы (4)


arrow_upward
6
arrow_downward

По возможности (в данном случае это возможно) используйте стандартные строковые функции, такие как SUBSTR, INSTR, TRANSLATE и т. д., вместо функций регулярных выражений. Регулярные выражения намного мощнее, но и требуют гораздо больше времени (именно по этой причине), поэтому их следует использовать только тогда, когда это действительно необходимо.

Если имя столбца str, то:

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

TRANSLATE преобразует z в себя, все цифры в NULL и все остальные символы в самих себя. (Увы, требуется буква z или НЕКОТОРЫЙ нецифровой символ.)

Ввод состоит из цифр тогда и только тогда, когда результат TRANSLATE равен 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
person mathguy    schedule 29.01.2018
comment
Ты прав; Я думал о TRANSLATE, но потом решил REGEXP_REPLACE, но последний действительно занимает в 1,5 раза больше времени с большой таблицей в моей базе данных. Жаль, что Oracle не считает '' просто пустым в TRANSLATE. Хотя я пришел к выводу, что подход Oracle VARCHAR2 '' is null, они плохо поработали с TRANSLATE в этом отношении. - person Thorsten Kettner; 29.01.2018
comment
Мне нужно использовать это в условиях, подобных ** SELECT COUNT(*) FROM ‹TABLE1› ONN, ‹TABLE2› SV WHERE SV.CSA_SHP_VISIT_STG_SEQ_ID=ONN.CSA_SHOP_VIST_SEQ_ID AND EXISTS (SELECT '1' FROM ‹TABLE3› TMP WHERE TRIM(SV .WORK_ORDER_NUM) = TRIM(TMP.WORK_ORDER_NUM) AND PLANT IN ('EWL') AND (случай, когда translate(TRIM(ONN.INSTLD_PART), 'z0123456789', 'z') имеет значение null, тогда ltrim(TRIM(ONN.INSTLD_PART) , '0') else TRIM(ONN.INSTLD_PART) end)=TRIM(TMP.INSTLD_PART) ); **. Я пробовал это, но это занимает больше времени. - person Selvathalapathy S; 29.01.2018
comment
@SelvathalapathyS - и... ?? - person mathguy; 29.01.2018

arrow_upward
3
arrow_downward

Используйте 1_.

Это заменяет строки, состоящие только из начальных нулей и конечных цифр, только последними цифрами, таким образом удаляя нули.

Пример запроса:

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

Результат:

COL       | NEWVALUE
----------+----------
000012345 | 12345
012321    | 12321
00012JY12 | 00012JY12
person Thorsten Kettner    schedule 29.01.2018

arrow_upward
0
arrow_downward

Если вы используете Oracle 12.2, вы можете использовать обработку ошибок выражения CAST.

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

Наряду с этим:

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

Замените <expression> именем вашего столбца (или любым другим). Я использую null в качестве магического значения в предложении default null on conversion error, но это не наносит вреда, так как ввод null будет просто соответствовать предложению THEN и передавать null.

person Markus Winand    schedule 29.01.2018
comment
Я использую Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. - person Selvathalapathy S; 29.01.2018

arrow_upward
0
arrow_downward

Вы можете создать функцию, которая проверяет, является ли она числовой или нет, см. эту ссылку для примера функции,

проверить, является ли это числовой функцией в Oracle

Вы можете TRIM нули числового значения, просто добавив к нему 0,

Образец кода:

--get  the IS_NUMERIC function from the link 

SELECT DECODE(IS_NUMERIC(col1), 'Y', col1+0, 'N', col1) 
  FROM your_table;
person eifla001    schedule 30.01.2018