在Oracle中如何判断一个值是否为非数字？

Question

我有以下代码，如果我的值无效，则返回错误消息。如果给定的值不是数字，我想提供相同的错误消息。

我有以下代码，如果我的值无效，则返回错误消息。如果给定的值不是数字，则希望提供相同的错误消息。

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

- Kyle Williamson

Answer 1

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

如果列只包含数字字符，则返回TRUE

- Rob van Laarhoven

---

当然，并非所有数字都完全由数字字符组成。"1.2"是数字，但它包含一个句点（或逗号，这取决于您的NLS设置）。"-100"是数字，但它包含负号。"1.2.3"虽然完全由数字和句点组成，但不是数字。如果您确实想要查看列是否具有无格式的正整数，则"regexp_like"可能已经足够了。 - Justin Cave

123e-5 也是一个数字。 - Shannon Severance

这个正则表达式只能匹配正整数值！ - Wernfried Domscheit

使用以下语句：SELECT column_with_int_and_string FROM MyTable WHERE NOT REGEXP_LIKE(column_with_int_and_string, ‘^[[:digit:]]+$’); - dkb

这个正则表达式可以根据需要进行扩展，如果需要的不仅仅是数字，则可以进行修改。但我认为这个答案能够满足OP的需求，并且对于任何遇到类似问题的人来说都是一个很好的起点。以下是一个更复杂的正则表达式示例，应该涵盖所有整数（未经测试）：'^[+-]?[[:digit:]]+$' - Jason

使用以下正则表达式来匹配十进制数：REGEXP_LIKE(COLUMN, '^-?[[:digit:].]*$')。 - Amjad Abu Saa

Answer 2

从 Oracle DB 12c Release 2 开始，您可以使用 VALIDATE_CONVERSION 函数：

VALIDATE_CONVERSION 确定 expr 是否可以转换为指定的数据类型。如果 expr 可以成功转换，则此函数返回 1；否则，此函数返回 0。如果 expr 求值为空，则此函数返回 1。如果在求值 expr 时发生错误，则此函数返回该错误。

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

db<>fiddle演示

- Lukasz Szozda

---

@MarvinM 很高兴听到这个消息。请还要查看DEFAULT ... ON CONVERSION ERROR。 - Lukasz Szozda

我不明白为什么这个函数在值为空时返回1。它应该是0。 - Salman

@Salman 可能是因为 SELECT CAST(null AS <datatype>) c FROM dual; 是一个有效的转换，所以它被定义为这样。 - Lukasz Szozda

VALIDATE_CONVERSION('' AS NUMBER) - 它返回1 - IS_NUMERIC('')也是一样的... - Marco Aurelio Fernandez Reyes

如果要检查的字符串没有小数部分，这个方法有效：CASE WHEN TO_NUMBER('') IS NOT NULL THEN 1 ELSE 0 END - 为什么"NULL"或空字符串是有效的/1？- 对我来说没有意义，说实话... - Marco Aurelio Fernandez Reyes

@MarcoAurelioFernandezReyes 在 Oracle 中 '' <=> NULL，而 NULL 是数字的完全有效值。SELECT TO_NUMBER(NULL)。如果您创建表 CREATE TABLE t(i INT)，则可以插入 INSERT INTO t VALUES (NULL) 而不会出现任何问题，并且列的数据类型仍然是数字。 - Lukasz Szozda

Answer 3

没有内置的函数，您可以编写一个。

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 )

- Justin Cave

---

如果我理解 OP 正确的话，我认为你想要在上面使用 is_numeric(str) = 0（相当于 !ISNUMERIC(value)）。 - David Faber

@DavidFaber - 我猜那是个笔误。如果一个字符串不是数值型的，将其与数值进行比较是没有意义的。 - Justin Cave

哦，我明白了，你在使用AND，而操作符应该是OR。我的错。 - David Faber

Answer 4

我在互联网上找到的最佳答案：

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

- TechDo

---

这种方法不适用于防止数字前、后甚至之间出现空格的场景。 - JamesHough

请查看此链接：https://www.techonthenet.com/oracle/questions/isnumeric.php - Udara Kasun

Answer 5

你可以使用以下正则表达式，它将匹配整数（例如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所建议的那样。

- David Faber

Answer 6

这个正则表达式可以匹配像 5，-5，+5，5.44，3.45e-3 这样的数字。

REGEXP_LIKE('54.55e33', '^[+-]?\d+([.]\d+)?(e[+-]?\d+)?$')

- Yasser Helmy

Answer 7

CREATE OR REPLACE FUNCTION IS_NUMERIC(P_INPUT IN VARCHAR2) RETURN INTEGER IS
  RESULT INTEGER;
  NUM NUMBER ;
BEGIN
  NUM:=TO_NUMBER(P_INPUT);
  RETURN 1;
EXCEPTION WHEN OTHERS THEN
  RETURN 0;
END IS_NUMERIC;
/

- harun ugur

Answer 8

SELECT DECODE(REGEXP_COUNT(:value,'\d'),LENGTH(:value),'Y','N') AS is_numeric FROM dual;

有很多方法，但这个对我来说非常完美。

- KOBER