Q
Problem solve Get help with specific problems with your technologies, process and projects.

Testing if a string is a number

How do you test if a string is a number?

The trick to doing this type of test is to use PL/SQL to see if the character string will easily convert to a number. If it won't the PL/SQL function will raise an error. I created a quick and simple function which will return '1' if the supplied string is a number, or '0' if it is not.

CREATE OR REPLACE FUNCTION is_a_number (char_string IN
VARCHAR2)
RETURN NUMBER
IS
   dummy_var NUMBER;
BEGIN
   SELECT TO_NUMBER(char_string) INTO dummy_var FROM
DUAL;
   RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;
/
The "trick" is that if the TO_NUMBER function is successful, then nothing is done except to return '1'. If the character string is not a number, then the TO_NUMBER function will raise an error. This will be trapped by the EXCEPTION clause, and '0' will be returned. To see it in action:
ORA9I SQL> select is_a_number('9876') from dual;

IS_A_NUMBER('9876')
-------------------
                  1

ORA9I SQL> select is_a_number('not_a_number') from
dual;

IS_A_NUMBER('NOT_A_NUMBER')
---------------------------
                          0
We can even use the DECODE function to make more use out of this function:
ORA9I SQL> select decode(is_a_number('123'),1,'A
Number!','Not a number') from dual;

DECODE(IS_A_
------------
A Number!

ORA9I SQL> select decode(is_a_number('abc'),1,'A
Number!','Not a number') from dual;

DECODE(IS_A_
------------
Not a number

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close