检查“这是一个数字" Oracle中的功能 [英] check if "it's a number" function in Oracle

问题描述

我正在尝试检查oracle(10g)查询中的列中的值是否为数字，以便进行比较.像这样:

I'm trying to check if a value from a column in an oracle (10g) query is a number in order to compare it. Something like:

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

关于如何检查的任何想法?

Any ideas on how to check that?

推荐答案

假定myTable中的ID列未声明为NUMBER(这似乎是一个奇怪的选择，可能有问题)，您可以编写一个试图将(可能是VARCHAR2)ID转换为数字，捕获异常并返回"Y"或"N"的函数.像

Assuming that the ID column in myTable is not declared as a NUMBER (which seems like an odd choice and likely to be problematic), you can write a function that tries to convert the (presumably VARCHAR2) ID to a number, catches the exception, and returns a 'Y' or an 'N'. Something like

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;

然后您可以将该呼叫嵌入查询中，即

You can then embed that call in a query, i.e.

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具有布尔数据类型，但是SQL没有.因此，尽管可以声明一个返回布尔值的函数，但不能在SQL查询中使用该函数.

Note that although PL/SQL has a boolean data type, SQL does not. So while you can declare a function that returns a boolean, you cannot use such a function in a SQL query.

这篇关于检查“这是一个数字" Oracle中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！