How to determine whether a string is a digital __oracle in Oracle

Method One: To judge by To_number function anomaly

CREATE OR REPLACE FUNCTION is_number (
Str_ VARCHAR2) return VARCHAR2
Is
NUM_ number;
BEGIN
Num_: = To_number (STR_);
Return ' Y ';
EXCEPTION
When others THEN
Return ' N ';
End Is_number;
/

Sql> Select Is_number (' 12345 ') from dual;

Is_number (' 12345 ')
--------------------------------------------------------------------------------
Y

Sql> Select Is_number (' 123.45 ') from dual;

Is_number (' 123.45 ')
--------------------------------------------------------------------------------
Y

Sql> Select Is_number (' 123a ') from dual;

Is_number (' 123A ')
--------------------------------------------------------------------------------
N

Sql>

Method two: Using Translate function to realize

SELECT nvl2 (Translate (' 123 ', '/1234567890 ', '/'), ' CHAR ', ' number ')
from dual;

Sql> SELECT nvl2 (Translate (' 123 ', '/1234567890 ', '/'), ' CHAR ', ' number ')
2 from dual;

NVL2 (TRANSLATE (' 123 ', '/1234567
------------------------------
Number

Sql>
Sql> SELECT nvl2 (Translate (' 123A ', '/1234567890 ', '/'), ' CHAR ', ' number ')
2 from dual;

NVL2 (TRANSLATE (' 123A ', '/123456
------------------------------
CHAR

Sql>

In the actual use of the process, can be selected according to the circumstances of these two methods. The advantage of the first method is that you can judge numbers with decimals, and the second method can only judge integers. Because if the Translate function is written in translate (' 123A ', '/', '/', '/') This form will be problematic.

Sql> Select Is_number (' 123.45.6 ') from dual;
 
Is_number (' 123.45.6 ')
----------------------------------------------------------------------- ---------
N
 
sql>
sql> SELECT nvl2 (Translate (' 1234.5.6 ', '/1234567890. ', '/'), ' CHAR ', ' Number ')
  2  from   dual;
&NBSP
NVL2 (TRANSLATE (' 1234.5.6 ', '/12
------------------------------
number
 
Sql>