"Безопасный" TO_NUMBER()

SELECT TO_NUMBER('*') FROM DUAL

Это, очевидно, дает мне исключение:

ORA-01722: недопустимый номер

Есть ли способ "пропустить" его и получить вместо него 0 или NULL?

Вся проблема: у меня есть поле NVARCHAR2, которое содержит числа и не почти;-) (например, *), и мне нужно выбрать наибольшее число из столбца.

Да, я знаю, что это ужасный дизайн, но это то, что мне нужно сейчас...: -S

UPD

Для себя я решил эту проблему с помощью

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)

Ответ 1

Я не мог найти ничего лучше этого:

function safe_to_number(p varchar2) return number is
    v number;
  begin
    v := to_number(p);
    return v;
  exception when others then return 0;
end;

Ответ 2

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0)

также получит числа со шкалой > 0 (цифры справа от десятичной точки).

Ответ 3

select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;

Он преобразует 123 в 123, но 123a или 12a3 до 0.

Ответ 4

Устанавливаем исходный вопрос и довольно старый skool

select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0)  from 
(
    select '1' a, 'not a number' b from dual
    union
    select '2' a, '1234' b from dual
)

Ответ 5

Вероятно, это немного беспорядочно сканирует ваше собственное регулярное выражение для проверки числа, но код ниже может работать. Я думаю, что другое решение от Gabe с использованием пользовательской функции более надежное, поскольку вы используете встроенную функциональность Oracle (и мое регулярное выражение, вероятно, не на 100% правильно), но это может стоить того:

with my_sample_data as (
  select '12345' as mynum from dual union all
  select '54-3' as mynum from dual union all
  select '123.4567' as mynum from dual union all
  select '.34567' as mynum from dual union all
  select '-0.3462' as mynum from dual union all
  select '0.34.62' as mynum from dual union all
  select '1243.64' as mynum from dual 
)
select 
  mynum, 
  case when regexp_like(mynum, '^-?\d+(\.\d+)?$') 
    then to_number(mynum) end as is_num
from my_sample_data

Затем вы получите следующий вывод:

MYNUM   IS_NUM
-------- ----------
12345   12345
54-3    
123.4567    123.4567
.34567  
-0.3462 -0.3462
0.34.62 
1243.64 1243.64

Ответ 6

select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
              null,
              DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
                     0,
                     DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
                            0,
                            TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
  from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;

Этот код исключает такие строки, как: -1-1, 1..1, 12-2 и т.д. И я не использовал регулярные выражения здесь.

Ответ 7

Из Oracle Database 12c Release 2 вы можете использовать TO_NUMBER с помощью DEFAULT ... ON CONVERSION ERROR:

SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

Или CAST:

SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;