假设test表中存在name字段
CREATE OR REPLACE FUNCTION ISNUMERIC1(MyStr VARCHAR2) RETURN NUMBER
IS
STR VARCHAR2(400);
ISNUM NUMBER;
NUM NUMBER;
BEGIN
ISNUM:=0;
STR:=TRIM(MyStr);
IF TRIM(STR) IS NULL THEN
return ISNUM;
END IF;
BEGIN
NUM:=TO_NUMBER(STR);
ISNUM:=1;
EXCEPTION
WHEN INVALID_NUMBER THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
return ISNUM;
END;
-------------第二种方式通过判断列数据长度,相同则返回true即表示全为数字,false则表示存在非数字。建议百度了解
translate函数作用即可理解本sql。语句如下
select * from test where length(translate(name,'-.0123456789'||name,'-.0123456789'))=length(name)