Finding rows that don't contain numeric data in Oracle

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

Solution 1:

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

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

Solution 3:

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.

Solution 4:

Use this

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

Solution 5:

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