ORACLE - 选择子查询上的计数

2022-01-23 00:00:00 sql count subquery oracle

我有一个包含一组范围(RangeA 和 RangeB)的 Oracle 表.这些列是 varchar，因为它们可以同时包含数字和字母数字值，如下例所示:

I've got an Oracle table that holds a set of ranges (RangeA and RangeB). These columns are varchar as they can hold both numeric and alphanumeric values, like the following example:

ID|RangeA|RangeB
1 |   10 |   20
2 |   21 |   30
3 | AB50 | AB70
4 | AB80 | AB90

我需要执行一个只返回具有数值的记录的查询，并对该查询执行计数.到目前为止，我已经尝试使用两个不同的查询来执行此操作，但没有任何运气:

I need to to do a query that returns only the records that have numeric values, and perform a Count on that query. So far I've tried doing this with two different queries without any luck:

查询1:

SELECT COUNT(*) FROM (
SELECT RangeA, RangeB FROM table R
WHERE upper(R.RangeA) = lower(R.RangeA)
) A
WHERE TO_NUMBER(A.RangeA) <= 10

查询 2:

WITH A(RangeA,RangeB) AS(
SELECT RangeA, RangeB FROM table 
WHERE upper(RangeA) = lower(RangeA)
)
SELECT COUNT(*) FROM A WHERE TO_NUMBER(A.RangeA) <= 10

子查询工作正常，因为我得到的两条记录只有数值，但查询的 COUNT 部分失败.我应该只得到 1，但我得到了以下错误:

The subquery is working fine as I'm getting the two records that have only numeric values, but the COUNT part of the query is failing. I should be getting only 1 on the count, but instead I'm getting the following error:

ORA-01722: invalid number
01722. 00000 -  "invalid number"

我做错了什么?非常感谢任何帮助.

What am I doing wrong? Any help is much appreciated.

推荐答案

可以用正则表达式测试每一列，判断是否为有效数字:

You can test each column with a regular expression to determine if it is a valid number:

SELECT COUNT(1)
FROM   table_of_ranges
WHERE  CASE WHEN REGEXP_LIKE( RangeA, '^-?d+(.d*)?$' )
            THEN TO_NUMBER( RangeA )
            ELSE NULL END
          < 10
AND    REGEXP_LIKE( RangeB, '^-?d+(.d*)?$' );

另一种选择是使用用户定义的函数:

Another alternative is to use a user-defined function:

CREATE OR REPLACE FUNCTION test_Number (
  str VARCHAR2
) RETURN NUMBER DETERMINISTIC
AS
  invalid_number EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_number, -6502);
BEGIN
  RETURN TO_NUMBER( str );
EXCEPTION
  WHEN invalid_number THEN
    RETURN NULL;
END test_Number;
/

那么你可以这样做:

SELECT COUNT(*)
FROM   table_of_ranges
WHERE  test_number( RangeA ) <= 10
AND    test_number( RangeB ) IS NOT NULL;

相关文章