[sql] 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 ...??

You can use following command -

LENGTH(TRIM(TRANSLATE(string1, '+-.0123456789', '')))

This will return NULL if your string1 is Numeric

your query would be -

select * from tablename 
where LENGTH(TRIM(TRANSLATE(X, '+-.0123456789', ''))) is null

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.

If you use Oracle 10 or higher you can use regexp functions as codaddict suggested. In earlier versions translate function will help you:

select * from tablename  where translate(x, '.1234567890', '.') is null;

More info about Oracle translate function can be found here or in official documentation "SQL Reference"

UPD: If you have signs or spaces in your numbers you can add "+-" characters to the second parameter of translate function.

The complete list of the regexp_like and other regexp functions in Oracle 11.1:

http://66.221.222.85/reference/regexp.html

In your example:

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

What about 1.1E10, +1, -0, etc? Parsing all possible numbers is trickier than many people think. If you want to include as many numbers are possible you should use the to_number function in a PL/SQL function. From http://www.oracle-developer.net/content/utilities/is_number.sql:

CREATE OR REPLACE FUNCTION is_number (str_in IN VARCHAR2) RETURN NUMBER IS
   n NUMBER;
BEGIN
   n := TO_NUMBER(str_in);
   RETURN 1;
EXCEPTION
   WHEN VALUE_ERROR THEN
      RETURN 0;
END;
/