How to Select Rows With Only Numeric Characters in Oracle SQL

Oracle: SQL query that returns rows with only numeric values

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> 

Oracle, show only rows where no numeric values appear

You can use Regular Expressions for pattern matching in Oracle.

SELECT
  *
FROM
  yourTable
WHERE
  NOT REGEXP_LIKE(x, '[0-9]+')

This will exclude any rows that have one or more numeric digits in column x.

How to find non-numeric columns containing only numeric data?

Create a function as this:

create or replace function isNumeric(val in VARCHAR2) return INTEGER AS
res NUMBER;
begin
   res := TO_NUMBER(val);
   RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

Then you can use it like this:

DECLARE
  r integer;
BEGIN
   For aCol in (SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_cols WHERE DATA_TYPE = 'VARCHAR2') LOOP
      -- What about CHAR and CLOB data types?
      execute immediate 'select count(*) from '||aCol.TABLE_NAME||' WHERE isNumeric('||aCol.COLUMN_NAME||') = 0' into r;
      if r = 0 then
         DBMS_OUTPUT.put_line(aCol.TABLE_NAME ||' '||aCol.COLUMN_NAME ||' contains numeric values only');
      end if;
   end loop;
end;

Note, the performance of this PL/SQL block will be poor. Hopefully this is a one-time-job only.

how to retrieve rows which contains only number?

Assuming that the column is 4 characters (as in the sample data), the only code that I can think of that works in both databases is:

select t.*
from tab1 t
where substr(t.col5, 1, 1) between '0' and '9' and
      substr(t.col5, 2, 1) between '0' and '9' and
      substr(t.col5, 3, 1) between '0' and '9' and
      substr(t.col5, 4, 1) between '0' and '9';

This is a bit brute force. But when getting SQL that works in multiple databases, you often have to limit the functionality that you use.

EDIT:

The function for string length is the same in Oracle and Teradata. So, for shorter strings you can do:

select t.*
from tab1 t
where substr(t.col5, 1, 1) between '0' and '9' and
      (length(t.col5) < 2 or substr(t.col5, 2, 1) between '0' and '9') and
      (length(t.col5) < 3 or substr(t.col5, 3, 1) between '0' and '9') and
      (length(t.col5) < 4 or substr(t.col5, 4, 1) between '0' and '9');

Oracle SQL: Filtering rows with non-numeric characters

I know you specifically asked for a regex solution, but translate can solve these kind of questions as well (and usually faster because regexes use more processing power):

select count(1)
from tbl
where translate(a, 'x0123456789', 'x') is null
and translate(b, 'x0123456789', 'x') is null;

What this does: translate the characters 0123456789 to null, and if the result is null, then the input must have been all digits. The 'x' is just there because the third argument to translate can not be null.

Thought I should add this here, might be helpful to other readers.

---

Related Topics