Oracle custom function to determine whether data is a numeric function, oracle Value

Oracle custom function to determine whether data is a numeric function, oracle Value

1 create or replace function isnumeric (str IN VARCHAR2) 2 return number 3 IS 4 v_str VARCHAR2 (1000); 5 BEGIN 6 IF str is null 7 THEN 8 RETURN 0; 9 ELSE10 v_str: = translate (str ,'. 0123456789 ','. '); 11 12 IF v_str = '. 'OR v_str =' +. 'OR v_str = '-. 'OR v_str IS NULL13 THEN14 RETURN str; 15 ELSE16 RETURN 0; 17 end if; 18 end if; 19 END isnumeric;

I. Syntax:
TRANSLATE (string, from_str, to_str)
Ii. Purpose
Replace each character in from_str with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank. Oracle interprets the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL.
Iii. Example
SQL code

1. select translate ('abcdefghj', 'abcdef', '123') FROM dual;
2. TRANSLATE (
3 .--------------
4. ghij 123456
5.
6. select translate ('abcdefghj', 'abcdefghj', '123') FROM dual;
7. TRANSL
8 .----------
9. 123456

Syntax: TRANSLATE (expr, from,)

Expr: represents a string of characters. from and to correspond one to one from left to right. If not, it is considered as a null value.

Example:

Select translate ('abcbbaadef ', 'ba',' # @ ') from dual (B will be replaced by #, and a will be replaced)

Select translate ('abcbbaadef ', 'bad',' # @ ') from dual (B will be replaced by #, a will be replaced by @, and d's value is null, will be removed)

Therefore, the results are as follows: @ # c ###@ def and @ # c ##@ ef.