-- Test Case

-- Create table and data

create table t1 (c1 varchar(8), c2 number(8));

insert into t1 values ('1234567a', 12345678);

insert into t1 values ('12345678', 12345678);

commit;

-- Error case (error occurs when converting to number and characters appear)

select * from t1
where c1 <> 12345678;
;

-5074:Given string does not represent a number in proper format.


-- Normal case 
select * from t1
where c1 <> '12345678';

C1               C2
-------- ----------
1234567a   12345678

1 row selected.

 

How to find data containing characters

-- Query 1 to find rows containing non-numeric characters
select * from t1
where translate(c1, '0123456789', ' ') is not null;

C1               C2
-------- ----------
1234567a   12345678

1 row selected.
 

-- Query 2 to find rows containing non-numeric characters
select * from t1
where not regexp_like(c1, '^\d+$');

C1               C2
-------- ----------
1234567a   12345678

After finding the strings, delete or update them and then execute the query again.