How to find row that has non-numeric value in a character column?

There can be a scenario where the datatype of the column is VARCHAR or CHAR but values that are suppose to be in there are numeric values in that column.

In the case when that happens and you would want to detect the row with that issue.

SQL> desc test;
Name Null? Type
—————————————– ——– ————–
X VARCHAR2(10)

SQL> select * from test;

X
———-
1
2
N/A
2A
10

Using one of the methods below you can identify the row(s).
Method 1: Works in 10g and up
SQL> select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[0-9]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 2: Works in 10g and up
select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[[:digit:]]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 3: Works in all version
SQL> set serveroutput on
SQL> declare
cursor l_cursor is select rowid, x from test;
l_num number;
begin
for l_rec in l_cursor
loop
begin
l_num := to_number(l_rec.x);
exception
when others then
dbms_output.put_line('Row id: ' || l_rec.rowid || ' ' || l_rec.x);
end;
end loop;
end;

Row id: AAE3GaAAEAAAQOYAAC N/A
Row id: AAE3GaAAEAAAQOYAAD 2A

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s