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 123Now, 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) -------------- 123Since 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 numberSince 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 numberIn 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.