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

SELECT TO_NUMBER('*') FROM DUAL

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

ORA-01722: неверный номер

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

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

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

UPD:

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

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)
Источник

9 ответов

Решение

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

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;
Источник

От 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;

db<> Fiddle demo

Источник 
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0)

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

Источник 
select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;

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

Источник

Подгонка оригинального вопроса и довольно старая школа

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
)
Источник

Вероятно, немного беспорядочно накатывать свои собственные регулярные выражения для проверки числа, но приведенный ниже код может сработать. Я думаю, что другое решение от 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
Источник 
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 и так далее. И я не использовал регулярные выражения здесь.

Источник

Комбинация предыдущих решений (от @sOliver и @Mike Meyers) и попытка собрать как можно больше чисел, удалив последний '$' из REGEXP.

Он может быть использован для фильтрации фактического числа из таблицы конфигурации и иметь комментарий типа "рядом" с номером "12 дней".

with my_sample_data as ( select '12345' as mynum from dual union all select '123.4567' as mynum from dual union all select '-0.3462' as mynum from dual union all select '.34567' as mynum from dual union all select '-.1234' as mynum from dual union all select '**' as mynum from dual union all select '0.34.62' as mynum from dual union all select '24Days' as mynum from dual union all select '42ab' as mynum from dual union all select '54-3' as mynum from dual ) SELECT mynum, COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num FROM my_sample_data;

даст


MYNUM    IS_NUM                                  
-------- ----------
12345    12345                                   
123.4567 123.4567                                
-0.3462  -0.3462                                 
.34567   0.34567                                 
-.1234   -0.1234                                 
**       0                                       
0.34.62  0.34                                    
24Days   24                                      
42ab     42                                      
54-3     54
Источник

Наилучшим методом, похоже, является функциональное решение, но если у вас нет необходимых привилегий в среде, с которой вы боретесь (как я), то вы можете попробовать это:

SELECT
 CASE
  WHEN
     INSTR(TRANSLATE('123O0',
                     ' qwertyuıopğüasdfghjklşizxcvbnmöçQWERTYUIOPĞÜASDFGHJKLŞİZXCVBNMÖÇ~*\/(){}&%^#$<>;@€|:_=',
                     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
                     ),
       'X') > 0
  THEN 'Y'
  ELSE 'N'
END is_nonnumeric
FROM DUAL

Кстати: в моем случае проблема была из-за "," и ".":) Так что примите это во внимание. Вдохновленный этим. Также этот кажется более кратким.

Кстати, 2: Уважаемый Oracle, не могли бы вы создать несколько встроенных функций для таких небольших, но бесценных нужд?

Источник
Другие вопросы по тегам