Oracle: SQL query that returns rows with only numeric values

Question

I have a field (column in Oracle) called X that has values like "a1b2c3", "abc", "1ab", "123", "156"

how do I write an sql query that returns me only the X that hold pure numerical values = no letters? from the example above would be „123“ and „156“

select X from myTable where ...??

Answer 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>  

Answer 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