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') --------------------------- 0We 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.