SQL中如何判断一个字符串是否为数字？

Question

在 Oracle 10g 上的 SQL 查询中，我需要确定字符串是否为数字。我该怎么做？

Answer 1

您可以使用 REGEXP_LIKE：

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?
, '') 

Answer 2

您可以尝试以下操作：

SELECT LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) FROM DUAL

其中 string1 是您正在评估的内容。如果是数字，它将返回 null。请参阅此处以获取进一步说明

Answer 3

我无法访问 10G 实例进行测试，但这在 9i 中有效：

CREATE OR REPLACE FUNCTION is_numeric (p_val VARCHAR2)
   RETURN NUMBER
IS
v_val   NUMBER;
BEGIN
   BEGIN
      IF p_val IS NULL OR TRIM (p_val) = ''
      THEN
         RETURN 0;
      END IF;

      SELECT TO_NUMBER (p_val)
        INTO v_val
        FROM DUAL;

      RETURN 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 0;
   END;
END;


SELECT is_numeric ('333.5') is_numeric
  FROM DUAL;

我假设您希望将空值/空值视为 FALSE。

Answer 4

正如 Tom Kyte 在 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7466996200346537833，如果您使用的是内置TO_NUMBER 在用户定义的函数中，您可能需要一些额外的技巧才能使其工作。

FUNCTION is_number(x IN VARCHAR2)
RETURN NUMBER
IS
    PROCEDURE check_number (y IN NUMBER)
    IS
    BEGIN
        NULL;
    END;
BEGIN
    PRAGMA INLINE(check_number, 'No');
    check_number(TO_NUMBER(x);
    RETURN 1;
EXCEPTION
    WHEN INVALID_NUMBER
    THEN RETURN 0;
END is_number;

问题是优化编译器可能认识到TO_NUMBER的结果没有在任何地方使用并优化它。

汤姆说（他的例子是关于日期而不是数字）：

禁用函数内联将使其调用
check_date 必须作为函数调用进行 - 使得
DATE 必须被推送到调用堆栈上。没有机会
在这种情况下，优化编译器删除对 to_date 的调用。如果
调用 check_date 所需的 to_date 调用失败
原因，我们知道字符串输入在该日期之前无法转换
格式。

Answer 5

这是一种确定数字的方法，可以作为简单查询的一部分，而无需创建函数。考虑嵌入空格、+- 不是第一个字符或第二个小数点。

var v_test varchar2(20);
EXEC :v_test := ' -24.9 ';

 select
 (case when trim(:v_test) is null then 'N' ELSE   -- only banks, or null
 (case when instr(trim(:v_test),'+',2,1) > 0 then 'N' ELSE  -- + sign not first char
 (case when instr(trim(:v_test),'-',2,1) > 0 then 'N' ELSE  -- - sign not first char
 (case when instr(trim(:v_test),' ',1,1) > 0 then 'N' ELSE  -- internal spaces
 (case when instr(trim(:v_test),'.',1,2) > 0 then 'N' ELSE  -- second decimal point
 (case when LENGTH(TRIM(TRANSLATE(:v_test, ' +-.0123456789',' '))) is not null then 'N' ELSE  -- only valid numeric charcters.
  'Y'
  END)END)END)END)END)END) as is_numeric
  from dual;

Answer 6

我发现该解决方案

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) is null

允许嵌入空白...它接受“123 45 6789”，就我的目的而言，这不是一个数字。

另一个级别的修剪/翻译可以纠正这个问题。以下将检测包含带有前导或尾随空格的连续数字的字符串字段，以便 to_number(trim(string1)) 不会失败

LENGTH(TRIM(TRANSLATE(translate(trim(string1),' ','X'), '0123456789', ' '))) is null

Answer 7

对于整数，您可以使用以下内容。第一个翻译将空格更改为字符，第二个翻译将数字更改为空格。如果仅存在数字，则 Trim 将返回 null。

TRIM(TRANSLATE(TRANSLATE(TRIM('1 2 3d 4'), ' ','@'),'0123456789',' ')) is null