Finding rows that don't contain numeric data in Oracle

Question

I am trying to locate some problematic records in a very large Oracle table. The column should contain all numeric data even though it is a varchar2 column. I need to find the records which don't contain numeric data (The to_number(col_name) function throws an error when I try to call it on this column).

Answer 1

In contrast to SGB's answer, I prefer doing the regexp defining the actual format of my data and negating that. This allows me to define values like $DDD,DDD,DDD.DD In the OPs simple scenario, it would look like

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');

which finds all non-positive integers. If you wau accept negatiuve integers also, it's an easy change, just add an optional leading minus.

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+$');

accepting floating points...

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?$');

Same goes further with any format. Basically, you will generally already have the formats to validate input data, so when you will desire to find data that does not match that format ... it's simpler to negate that format than come up with another one; which in case of SGB's approach would be a bit tricky to do if you want more than just positive integers.

Answer 2

To get an indicator:

DECODE( TRANSLATE(your_number,' 0123456789',' ')

e.g.

SQL> select DECODE( TRANSLATE('12345zzz_not_numberee',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"contains char"

and

SQL> select DECODE( TRANSLATE('12345',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

and

SQL> select DECODE( TRANSLATE('123405',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

Oracle 11g has regular expressions so you could use this to get the actual number:

SQL> SELECT colA
  2  FROM t1
  3  WHERE REGEXP_LIKE(colA, '[[:digit:]]');

COL1
----------
47845
48543
12
...

If there is a non-numeric value like '23g' it will just be ignored.

Answer 3

I was thinking you could use a regexp_like condition and use the regular expression to find any non-numerics. I hope this might help?!

SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');

Answer 4

I tray order by with problematic column and i find rows with column.

SELECT 
 D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND,
          D.COL1  AS COL1


FROM
  VW_DATA_ALL_GC  D
  
  WHERE
  
   (D.PERIOADA IN (:pPERIOADA))  AND   
   (D.FORM = 62) 
   AND D.COL1 IS NOT NULL
 --  AND REGEXP_LIKE (D.COL1, '\[\[:alpha:\]\]')
 
-- AND REGEXP_LIKE(D.COL1, '\[\[:digit:\]\]')
 
 --AND REGEXP_LIKE(TO_CHAR(D.COL1), '\[^0-9\]+')
 
 
   GROUP BY 
    D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND ,
          D.COL1  
         
         
        ORDER BY 
        D.COL1

Answer 5

You can use this one check:

create or replace function to_n(c varchar2) return number is
begin return to_number(c);
exception when others then return -123456;
end;

select id, n from t where to_n(n) = -123456;

Answer 6

After doing some testing, i came up with this solution, let me know in case it helps.

Add this below 2 conditions in your query and it will find the records which don't contain numeric data

 and REGEXP_LIKE(<column_name>, '\D') -- this selects non numeric data
 and not REGEXP_LIKE(column_name,'^[-]{1}\d{1}') -- this filters out negative(-) values

Answer 7

After doing some testing, building upon the suggestions in the previous answers, there seem to be two usable solutions.

Method 1 is fastest, but less powerful in terms of matching more complex patterns.
Method 2 is more flexible, but slower.

Method 1 - fastest
I've tested this method on a table with 1 million rows.
It seems to be 3.8 times faster than the regex solutions.
The 0-replacement solves the issue that 0 is mapped to a space, and does not seem to slow down the query.

SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;

Method 2 - slower, but more flexible
I've compared the speed of putting the negation inside or outside the regex statement. Both are equally slower than the translate-solution. As a result, @ciuly's approach seems most sensible when using regex.

SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');

Answer 8

Starting with Oracle 12.2 the function to_number has an option ON CONVERSION ERROR clause, that can catch the exception and provide default value.

This can be used for the test of number values. Simple set NULL when the conversion fails and filer all not NULL values.

Example

with num as (
select '123' vc_col from dual union all
select '1,23'  from dual union all
select 'RV12P2000'  from dual union all
select null  from dual)
select
  vc_col 
from num
where /* filter numbers */
vc_col is not null and
to_number(vc_col DEFAULT NULL ON CONVERSION ERROR) is not null
;

VC_COL   
---------
123
1,23

Answer 9

I've found this useful:

 select translate('your string','_0123456789','_') from dual

If the result is NULL, it's numeric (ignoring floating point numbers.)

However, I'm a bit baffled why the underscore is needed. Without it the following also returns null:

 select translate('s123','0123456789', '') from dual

There is also one of my favorite tricks - not perfect if the string contains stuff like "*" or "#":

 SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')

Answer 10

Use this

SELECT * 
FROM TableToSearch 
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?$');

Answer 11

From http://www.dba-oracle.com/t_isnumeric.htm

LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null

If there is anything left in the string after the TRIM it must be non-numeric characters.