Oracle: SQL query that returns rows with only numeric values

Solution 1:

You can use the REGEXP_LIKE function as:

SELECT X 
FROM myTable 
WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');

Sample run:

SQL> SELECT X FROM SO;

X
--------------------
12c
123
abc
a12

SQL> SELECT X  FROM SO WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');

X
--------------------
123

SQL> 

Solution 2:

If the only characters to consider are letters then you can do:

select X from myTable where upper(X) = lower(X)

But of course that won't filter out other characters, just letters.

Since Oracle 12c (at least) there has been a built-in function to check whether a character value is numeric: VALIDATE_CONVERSION

select X from myTable where validate_conversion(X as number) = 1