Как вы можете определить, не является ли значение не числовым в Oracle?

У меня есть следующий код, который возвращает сообщение об ошибке, если мое значение недействительно. Я хотел бы дать такое же сообщение об ошибке, если указанное значение не является числовым.

IF(option_id = 0021) THEN 
      IF((value<10000) or (value>7200000) or /* Numeric Check */)THEN
          ip_msg(6214,option_name);  -- Error Message
          return;
      END IF;
END IF;

В SQL Server я просто использовал ISNUMERIC(). Я хотел бы сделать что-то подобное в Oracle. Например,

IF((!ISNUMERIC(value)) or (value<10000) or (value>7200000))
    THEN ...

Ответ 1

REGEXP_LIKE(column, '^[[:digit:]]+$')

возвращает TRUE, если в столбце содержатся только числовые символы

Ответ 2

Там нет встроенной функции. Вы могли бы написать один

CREATE FUNCTION is_numeric( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 1;
EXCEPTION
  WHEN value_error
  THEN
    RETURN 0;
END;

и/или

CREATE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN l_num;
EXCEPTION
  WHEN value_error
  THEN
    RETURN NULL;
END;

Вы можете тогда сделать

IF( is_numeric( str ) = 1 AND 
    my_to_number( str ) >= 1000 AND
    my_to_number( str ) <= 7000 )

Если вы используете Oracle 12.2 или новее, в функции to_number есть усовершенствования, которые вы могли бы использовать

IF( to_number( str default null on conversion error ) >= 1000 AND
    to_number( str default null on conversion error ) <= 7000 )

Ответ 3

Из Oracle DB 12c Release 2 вы можете использовать функцию VALIDATE_CONVERSION:

VALIDATE_CONVERSION определяет, можно ли преобразовать expr в указанный тип данных. Если expr может быть успешно преобразован, то эта функция возвращает 1; в противном случае эта функция возвращает 0. Если expr имеет значение null, то эта функция возвращает 1. Если при вычислении expr возникает ошибка, то эта функция возвращает ошибку.

 IF (VALIDATE_CONVERSION(value AS NUMBER) = 1) THEN
     ...
 END IF;

ДБ <> Fiddle Demo

Ответ 4

Вы можете использовать следующее регулярное выражение, которое будет соответствовать целым числам (например, 123), числа с плавающей запятой (12.3) и числам с показателями (1.2e3):

^-?\d*\.?\d+([eE]-?\d+)?$

Если вы хотите принимать знаки +, а также знаки - (как это делает Oracle с помощью TO_NUMBER()), вы можете изменить каждое вхождение - выше на [+-]. Поэтому вы можете переписать свой блок кода следующим образом:

IF (option_id = 0021) THEN 
    IF NOT REGEXP_LIKE(value, '^[+-]?\d*\.?\d+([eE][+-]?\d+)?$') OR TO_NUMBER(value) < 10000 OR TO_NUMBER(value) > 7200000 THEN
        ip_msg(6214,option_name);
        RETURN;
    END IF;
END IF;

Я не совсем уверен, что будет обрабатывать все значения, поэтому вы можете добавить блок EXCEPTION или написать пользовательскую функцию TO_NUMBER(), как предлагает @JustinCave.

Ответ 5

Лучший ответ, который я нашел в интернете:

SELECT case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
       end
FROM tab1;