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

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:]]$');

Answer 2

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 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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;
/