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

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

Leave a Comment

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