Finding characters in a string (VARCHAR-VARCHAR2) column

VijayaTech Labs Blog
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

Share this post with your friends

Leave a Comment

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

Scroll to Top