DEVFYI - Developer Resource - FYI

How to implement ISNUMERIC function in SQL *Plus ?

PL/SQL Interview Questions and Answers

(Continued from previous question...)

How to implement ISNUMERIC function in SQL *Plus ?

Method 1:

Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:

select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

It returns 0 if it is a number, 1 if it is not.

(Continued on next question...)

Other Interview Questions