Help with finding Chars in a Number field

[johng]

I am doing a TO_NUMBER script. there is a row that has a Canadian zip code, which contains characters. Oracle returns an error message of invalid number. Is there any way i can get the Row ID or primary key of the offending row?

thanks

John

[m1l]

You could try

select primary_key
from table
where instr(translate(field_to_test, 'abcdefghijklmnopqrstuvwxyz', '**************************'), '*') != 0

[marist89]

This is the bute force method:

set serveroutput on;
declare
tmpnum integer;
txtnum varchar2(5);
tmp_pk varchar2(5);
begin
for rs in (select pk_field, text_field from tablename) loop
txtnum := rs.text_field;
tmp_pk := rs.pk_field;
tmpnum := to_number(rs.text_field);
end loop;
exception
when others then
dbms_output.put_line(txtnum || ' is the invalid number.' || tmp_pk || ' is the key.');
raise;
end;
/

Here, you select your primary key field along with the field you want to convert. When it tries to convert your alpha field to numeric, an exception will get thrown and you can print out any information about the row (including the pk).

[johng]

THANK YOU BOTH :):):):)