检查是否“这是一个数字"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屋！