Compile the following Function in Oracle Database by copying it and Pressing F5 or F9
create or replace function test_num (v_card varchar2) return varchar2
is
v_num number;
begin
select v_card into v_num from dual;
return 1;
exception
when others then
return v_card;
–DBMS_OUTPUT.PUT_LINE(v_card);
–DBMS_OUTPUT.PUT_LINE(SQLCODE);
–DBMS_OUTPUT.PUT_LINE(SQLERRM);
end test_num;
Now fire the query as follows to find the record which contain characters other than Numbers
select * from (
select test_num(card) cardno from rtemp)
where cardno<>’1′;
Note:
- rtemp is table name
- card is the column name in which you want to find characters