IsNumeric in Oracle Database

By Santhosh N

Used to check if the column value is in numeric format

I am sure you are aware of the fact that there is no IsNumeric function in oracle and to accomplish this here is the way..

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

this returns zero if it is a number or greater than zero if not numeric.

NOTE: There is a space before the + (PLUS symbol) in the second parameter of the translate,
and a single space inthe last argument.

Related FAQs

What is the real purpose of dual in oracle.
This explains how to convert the string to date format in oracle.
There are four types of Datatypes for date in oracle including the support to the time and timezone info alongwith daylightsavings time where applicable
This explains how to find the length of the string in oracle
Oracle has a function to give the position of the char or string inside the string
This explains how to convert the the given date or integer values to strings.
IsNumeric in Oracle Database  (10969 Views)