Finding rows that don't contain numeric data in Oracle

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

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

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

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