Finding rows that don't contain numeric data in Oracle

sql oracle varchar numeric varchar2
Ben picture Ben · Oct 31, 2011 · Viewed 105.2k times · Source

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

SGB picture SGB · Oct 31, 2011

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

Related questions

Difference between BYTE and CHAR in column datatypes

In Oracle, what is the difference between : CREATE TABLE CLIENT ( NAME VARCHAR2(11 BYTE), ID_CLIENT NUMBER ) and CREATE TABLE CLIENT ( NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11) ID_CLIENT NUMBER )

Read More
What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

I know that I can declare a varchar2 using the number of the characters that it should be able to contain. However, in an Oracle database on which I am working, I found that a field (named PDF) is defined …

Read More
alternative to listagg in Oracle?

listagg is a function introduced in Oracle 11.2! now this function is bugging us allot, we are migrating from MySQL to Oracle and we have this query: SELECT p_id, MAX(registered) AS registered, listagg(MESSAGE, ' ') within GROUP (ORDER …

Read More