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

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

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

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

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

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

Examples related to sql

Examples related to oracle