检查Oracle中是否存在"it's a number“函数

Question

我正在尝试检查oracle (10g)查询中某列的值是否为数字，以便进行比较。类似于：

select case when ( is_number(myTable.id) and (myTable.id >0) ) 
            then 'Is a number greater than 0' 
            else 'it is not a number' 
       end as valuetype  
  from table myTable

对如何检查有什么建议吗？

Answer 1

假设myTable中的ID列没有声明为数字(这似乎是一个奇怪的选择，可能会有问题)，您可以编写一个函数，尝试将ID (可能是VARCHAR2)转换为数字，捕获异常，并返回一个'Y‘或'N’。就像这样

CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 )
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 'Y';
EXCEPTION
  WHEN value_error THEN
    RETURN 'N';
END is_number;

然后，可以将该调用嵌入到查询中，即

SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0 
               THEN 'Number > 0'
             ELSE 'Something else'
         END) some_alias
  FROM myTable

请注意，尽管PL/SQL具有boolean数据类型，但SQL没有。因此，虽然可以声明返回布尔值的函数，但不能在SQL查询中使用这样的函数。

Answer 2

here提到的另一个想法是使用正则表达式来检查：

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^[[:digit:]]+$');

好的部分是您不需要单独的PL/SQL函数。潜在的问题部分是，对于大量的行，正则表达式可能不是最有效的方法。

Answer 3

Saish的答案是使用REGEXP_LIKE是正确的想法，但不支持浮点数。这个会..。

返回值为数值

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

返回值不是数字

SELECT  foo 
FROM    bar
WHERE   NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

您可以测试您的正则表达式本身，直到您对换行符感到满意为止(但请确保在此复选框中选中match at http://regexpal.com/ )。