问题背景,to_number(...)中只能接受数字如122或数字型字符串'122',接受'aaa'会报错“无效字符”。
1.函数方法
CREATE OR REPLACE FUNCTION IS_NUMBER(IN_PRICE IN VARCHAR2) RETURN NUMBER IS
N_PRICE NUMBER;
N_IS NUMBER;
BEGIN
N_PRICE := NVL(TO_NUMBER(IN_PRICE), 0);
N_IS := 1;
RETURN N_IS;
EXCEPTION
WHEN OTHERS THEN
N_IS := 0;
RETURN N_IS;
END;
SELECT *
FROM MOD_BZLSDDXX_EXCEL A
WHERE A.APP_NO_EXCEL = '0116030725612068'
AND IS_NUMBER(a.cpgpj) = 0 for UPDATE;
2.translate
SELECT nvl2(translate(333,'0123456789/','/'),'not number','is number') FROM dual;