Trapping unknown characters in a column
Posted on 2014-02-05
I have a column which has characters which are unknown - may be control characters or some other junk. I would like to find the specific row with the column with this problem. When I do a function such as to_number or to_date, it gives an error but doesn't tell the exact row. I have a counter column which I can use which does take some time. The dump function does the same thing, that is, errors out. The error is ORA-01841 when inserting into a date column. Is there a way to run one SELECT and find the specific row? Something like:
select * from Tbl1 where instr(dump(Col1),'20')=0
This column is supposed to have an 8 digit number only which gets translated into date.