更新时间:2023-11-06 16:32:22
您可以使用正则表达式测试每一列以确定其是否为有效数字:
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;