CREATEORREPLACEFUNCTION isnumeric (p_num INVARCHAR2) RETURNNUMBERAS l_tst NUMBER; BEGIN l_tst := TO_NUMBER(p_num); RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END;
正則方法二 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATEORREPLACEFUNCTION isnumeric(strINVARCHAR2) RETURNNUMBER IS BEGIN IFstrISNULLTHEN RETURN0; ELSE IF regexp_like(str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$') THEN RETURN 1; ELSE RETURN 0; ENDIF; ENDIF; END isnumeric;
CREATEORREPLACEFUNCTION ISNUMERIC(MyStr VARCHAR2) RETURNNUMBER IS strVARCHAR2(400); isnum NUMBER; num NUMBER; BEGIN isnum := 0; str := TRIM(MyStr); IF TRIM(str) IS NULL THEN GOTO NON_NUMBER; ENDIF;
BEGIN num := TO_NUMBER(str); isnum := 1; EXCEPTION WHEN INVALID_NUMBER THEN NULL; WHEN OTHERS THEN NULL; END; <<NON_NUMBER>> RETURN isnum; END;