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

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


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