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

codaddict · Accepted Answer · 2010-10-14T12:57:10.827

85

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>

edited Oct 14 '10 at 12:57

answered Oct 14 '10 at 10:38

codaddict

445,704
82
492
529

You can use regexp in Oracle 10i as well – andr Oct 14 '10 at 12:47
@andr: Thanks :) I tried it on a 11g box, so was not sure when did Oracle introduce regex. – codaddict Oct 14 '10 at 12:48
This test have the advantage of stripping the null values compared to LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))). – Jean-Philippe Martin Nov 01 '12 at 14:31
Does this still work? We have a 12c database yet this doesn't seem to work to only select rows that give back numbers for a column. – The God Emperor Sep 22 '14 at 16:06

Tony Andrews · Answer 2 · 2021-07-21T07:07:44.847

38

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.

Since Oracle 12c (at least) there has been a built-in function to check whether a character value is numeric: VALIDATE_CONVERSION

select X from myTable where validate_conversion(X as number) = 1

edited Jul 21 '21 at 07:07

answered Oct 14 '10 at 11:02

Tony Andrews

129,880
21
220
259

3

Note that this solution is much faster than regexp or function approach: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1132906300346063525 – Vadzim Oct 10 '14 at 16:15

score 8 · Answer 3 · answered Oct 14 '10 at 12:53

8

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.

answered Oct 14 '10 at 12:53

andr

1,626
8
10

If you String-Dates in your column like `2019-05-15` (in case you added +-) or `25.25.219` or other numbers with multiple dots in them like formatted Customer numbers `201.20111.56`, IP-Addresses `192.168.1.1`, etc., they will be in your result. You cannot automatically assume that a `to_number` function will succeed. – emi-le May 15 '19 at 14:15

score 5 · Answer 4 · answered Oct 15 '10 at 02:33

5

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

answered Oct 15 '10 at 02:33

Jon Heller

34,999
6
74
132

This addresses a different question. He didn't say numbers, he said digits. – EvilTeach Feb 17 '12 at 16:51
I interpreted "numeric values" to mean "number". I was thinking of the context of converting strings to numbers; a situation where you really need to use PL/SQL to prevent out-of-order execution causing "invalid number" errors. If he really just want to check digits, then you're right, this won't work. – Jon Heller Feb 18 '12 at 02:37

score 4 · Answer 5 · edited Sep 16 '15 at 07:10

4

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

edited Sep 16 '15 at 07:10

slavoo

5,798
64
37
39

answered Oct 14 '10 at 10:37

Siggi

49
3

This will not work, it'll report rows that have a digit anywhere. You need to use anchors. – codaddict Oct 14 '10 at 10:44
1

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm has some useful info – Chris Young Jul 17 '13 at 21:14

score 3 · Answer 6 · answered Oct 14 '10 at 10:38

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

answered Oct 14 '10 at 10:38

Sachin Shanbhag

54,530
11
89
103

1

Cite from official SQL Reference documentation: "You cannot use an empty string for to_string to remove all characters in from_ string from the return value". So you can't use empty string as the third argument in TRANSLATE(string1, '+-.0123456789', '') – andr Oct 14 '10 at 12:49
1

I think the third argument needs to be a space: ' ' – Hugh Seagraves Nov 20 '18 at 17:28

Oracle: SQL query that returns rows with only numeric values

6 Answers6

Linked