If you ever encounter a character column that should be a numeric column, there’s always a possibility that it contains non-numeric data that you don’t know about.

In Oracle Database, you can run a query like the following to return non-numeric data from the column.

Sample Data

Let’s create a sample table with a VARCHAR2 column and insert data:

DROP TABLE t1;
CREATE TABLE t1 (
    c1 varchar2(255)
);
INSERT ALL 
INTO t1 (c1) VALUES ('0')
INTO t1 (c1) VALUES ('1')
INTO t1 (c1) VALUES ('+1')
INTO t1 (c1) VALUES ('-1')
INTO t1 (c1) VALUES ('00.00')
INTO t1 (c1) VALUES ('73.45')
INTO t1 (c1) VALUES ('+73.45')
INTO t1 (c1) VALUES ('-73.45')
INTO t1 (c1) VALUES ('.246')
INTO t1 (c1) VALUES ('-.34e7')
INTO t1 (c1) VALUES ('12.e-3')
INTO t1 (c1) VALUES ('1.2e+4')
INTO t1 (c1) VALUES ('a')
INTO t1 (c1) VALUES ('9afc')
INTO t1 (c1) VALUES ('e7')
INTO t1 (c1) VALUES ('+e0')
INTO t1 (c1) VALUES ('Ten')
INTO t1 (c1) VALUES ('5 Dollars')
SELECT 1 FROM DUAL;

Here’s what the table contains:

SELECT * FROM t1;

Result:

0
1
+1
-1
00.00
73.45
+73.45
-73.45
.246
-.34e7
12.e-3
1.2e+4
a
9afc
e7
+e0
Ten
5 Dollars

The column is a varchar(255) column, so it’s not numeric. It can (and does) contain numbers but these are stored as character data. It can also contain arbitrary text (which it does).

Return All Non-Numeric Values

We can use the following query to return non-numeric values from the above table:

SELECT c1
FROM t1 
WHERE NOT REGEXP_LIKE(c1, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

Result:

12.e-3
a
9afc
e7
+e0
Ten
5 Dollars

Return Non-Integers

If we only want to return non-integers, the query can be a lot simpler:

SELECT c1
FROM t1 
WHERE NOT REGEXP_LIKE(c1, '^[0-9]+$');

Result:

+1
-1
00.00
73.45
+73.45
-73.45
.246
-.34e7
12.e-3
1.2e+4
a
9afc
e7
+e0
Ten
5 Dollars

Note that this example also excludes signed integers.

Does Not Contain Numeric Data

If we just want to find rows that don’t contain any numeric data, we can do the following:

SELECT c1 
FROM t1 
WHERE NOT REGEXP_LIKE(c1, '[0-9]+');

Result:

a
Ten

We can alternatively use POSIX classes to get the same result:

SELECT c1 
FROM t1 
WHERE NOT REGEXP_LIKE(c1, '[[:digit:]]');

Result:

a
Ten