Finding characters in a string (VARCHAR-VARCHAR2) column

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


v_num number;


select v_card into v_num from dual;

return 1;


when others then 

return v_card;




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′;


  • rtemp is table name 
  • card is the column name in which you want to find characters

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top

We're Hiring

join Us

We’re looking for passionate Lead Content writer & Graphic designer.