fbpx

Finding characters in a string (VARCHAR-VARCHAR2) column

VijayaTech Labs Blog
Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
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

Share this post with your friends

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on skype
Share on telegram
Share on whatsapp
Share on email

Leave a Comment

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

Scroll to Top