我有一个包含varchar类型的数据库。
我想知道哪些记录保存了数字值。我尝试了REGEXP_COUNT和其他方法，但我的数据库版本是9i，我认为这些方法是针对10g及以上版本的。
我该如何实现这个功能？
我已经尝试过：
select to_number( my_column ) from my_table
但是它不起作用，因为并非所有的员工ID都是数字。
编辑
背景。
这个表格包含员工ID，所有的员工ID都是数字（如1234、24523或6655）。
在初始数据库加载时，当员工ID未知时，他们使用了文本而不是像-1这样的内容：
NA, N/A, NONE, UNK, UNKNOW, TEST, EXTERNAL, WITHOUT_ID
实际上，主要问题在于该列应该是数字，而不是varchar。
现在，我试图获取所有不是数字的记录（不包含员工ID），但由于该数据库是9i版本，无法使用RegExp。
很抱歉，您需要编写自己的isnumber函数，并使用它，类似于这样（未经测试）在这个线程中找到的代码，应该可以工作。
DECLARE FUNCTION isNumber(p_text IN VARCHAR2) RETURN NUMBER IS
v_dummy NUMBER;
not_number EXCEPTION;
PRAGMA EXCEPTION_INIT(-, not_number);
BEGIN
v_dummy := TO_NUMBER(p_text);
RETURN 1;
EXCEPTION
WHEN not_number THEN RETURN 0;
END is_number;
又一个纯SQL解决方法：
select my_column
from my_table
where translate(my_column,'x0123456789','x') is null;
这取决于你认为什么是“数字”。你允许负数、小数或只允许整数，还是科学计数法（例如“1e3”）？前导零是否允许？
如果你只想要正整数值，请尝试
where translate(col,' 1234567890','0') is null
试试这个
CREATE OR REPLACE PACKAGE value_tests
AS
FUNCTION get_number( pv_value IN VARCHAR2 ) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY value_tests
AS
FUNCTION get_number( pv_value IN VARCHAR2 ) RETURN NUMBER
IS
converted_number NUMBER;
invalid_number EXCEPTION;
PRAGMA EXCEPTION_INIT( invalid_number, -01722 );
value_error EXCEPTION;
PRAGMA EXCEPTION_INIT( value_error, -06502 );
BEGIN
<<try_conversion>>
BEGIN
converted_number := TO_NUMBER( pv_value );
EXCEPTION
WHEN invalid_number OR value_error
THEN
converted_number := NULL;
END try_conversion;
RETURN converted_number;
END get_number;
END;
/
在这上面运行它...
select my_column
, value_tests.get_number( my_column ) my_column_num
from ( select 'mydoghas3legs' my_column from dual
union all select '27.5' my_column from dual
union all select '27.50.5' my_column from dual
)
返回
MY_COLUMN MY_COLUMN_NUM
------------- -------------
mydoghas3legs
27.5 27.5
27.50.5
我不喜欢在正常代码中使用异常，但这似乎是最好和最安全的方法：
CREATE OR REPLACE FUNCTION "IS_NUMBER" (pX in varchar2) return integer is
n number;
begin
n:=to_number(pX);
return 1;
exception
when others then
return 0;
end;
create or replace function IsNumber(
a_Text varchar2
) return char is
t_Test number;
begin
begin
t_Test := to_number(a_Text);
return 'Y';
exception when value_error then
return 'N';
end;
end;
使用示例：
select IsNumber('zzz') from dual;
结果：N
select IsNumber('123.45') from dual;
结果：Y
select my_column
from my_table
where my_column not like '%1%'
and my_column not like '%2%'
and my_column not like '%3%'
and my_column not like '%4%'
and my_column not like '%5%'
and my_column not like '%6%'
and my_column not like '%7%'
and my_column not like '%8%'
and my_column not like '%9%'
and my_column not like '%0%'
有点脏，但是能用。;)
另一种方法，这里是我之前写的一个函数：
CREATE OR REPLACE function string_is_numeric
(p_string_in in varchar2)
return boolean is
begin
for i in 1..length(p_string_in) loop
if substr(p_string_in, i, 1) not in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') then
return false;
end if;
end loop;
return true;
end;
/