Checking if varchar2 column contains only numbers
What is the quickest way of checking if a varchar2 column contains only numeric values?
Use the TO_NUMBER function on the column. It will generate an error if the column contains more than just numbers. For instance, I'll set up a quick table with two rows of data:
ORA9I SQL> create table test (val varchar2(10));
Table created.
ORA9I SQL> insert into test values ('abc');
1 row created.
ORA9I SQL> insert into test values (123);
1 row created.
ORA9I SQL> commit;
Commit complete.
ORA9I SQL> select * from test;
VAL
----------
abc
123
Now, I'll use the TO_NUMBER function to determine if this column has a row with non-number values.
ORA9I SQL> select to_number(val) from test where
val='123';
TO_NUMBER(VAL)
--------------
123
Since this row only contain numbers in that column, a valid value was returned.
ORA9I SQL> select to_number(val) from test where
val='abc';
select to_number(val) from test where val='abc'
*
ERROR at line 1:
ORA-01722: invalid number
Since this row did not contain valid numbers in the column an ORA-1722 error was raised.
ORA9I SQL> select to_number(val) from test;
select to_number(val) from test
*
ERROR at line 1:
ORA-01722: invalid number
In the above query, I used every row in the table (there is no WHERE clause). This ORA-1722 error tells me that some row in the table does not have a valid number. It doesn't tell me which row in the table though. But it is a quick and easy test.
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.
