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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the REGEXP LIKE function as   SELECT X  FROM myTable  WHERE REGEXP LIKE X       digit           Sample run   SQL gt  SELECT X FROM SO   X -------------------- 12c 123 abc a12  SQL gt  SELECT X  FROM SO WHERE REGEXP LIKE X       digit          X -------------------- 123  SQL gt

[sql] Oracle: SQL query that returns rows with only numeric values

Examples related to sql

Examples related to oracle