January 29, 2011

Taking number form string

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;

No comments:

Post a Comment

Thanks for your comments submitted.,will review and Post soon! by admin.

Oracle HCM Extracts — A Practical Guide to Building Your First Integration Extract

  HCM Extracts are Oracle's built-in mechanism for extracting structured HR data for payroll interfaces, benefits carriers, and third-pa...