sqloracle-database

Oracle: SQL query that returns rows with only numeric values

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 ...??

Solution

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