The below code will fecth the number in a string:
CREATE OR REPLACE FUNCTION GET_NUMBER_FROM_STRING(STR1 VARCHAR2) RETURN NUMBER
IS
lv_num number(20);
BEGIN
SELECT TO_NUMBER(TRIM(TRANSLATE(LOWER(STR1),'abcdefghijklmnopqrstuvwxyz/-+',' '))) INTO lv_num FROM dual;
RETURN(lv_num);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
END;
e.g
select GET_NUMBER_FROM_STRING('AD223445') from dual;
CREATE OR REPLACE FUNCTION GET_NUMBER_FROM_STRING(STR1 VARCHAR2) RETURN NUMBER
IS
lv_num number(20);
BEGIN
SELECT TO_NUMBER(TRIM(TRANSLATE(LOWER(STR1),'abcdefghijklmnopqrstuvwxyz/-+',' '))) INTO lv_num FROM dual;
RETURN(lv_num);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
END;
e.g
select GET_NUMBER_FROM_STRING('AD223445') from dual;
No comments:
Post a Comment
Thanks for your comments submitted.,will review and Post soon! by admin.