The following examples return only those rows that have numeric values in a given column in Oracle Database.

These examples use the REGEXP_LIKE function to return values that match the specified pattern.

Sample Data

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

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;

Let’s select all data from the table:

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 varchar2 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 Numeric Values

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

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

Result:

0
1
+1
-1
00.00
73.45
+73.45
-73.45
.246
-.34e7
1.2e+4

Return Integers

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

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

Result:

0
1

Contains Numeric Data

If we want to find rows that contain numeric data (even if they also contain non-numeric data), we can do the following:

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

Result:

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

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

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

Result:

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