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 AI Agents: Real-Time Leave Analytics & Smart Employee Workspaces — Live on Fusion HCM

🔴 Oracle AI Agent Studio · Case Study Oracle HCM AI Agents: Real-Time Leave Analytics & Smart Employee Workspaces — Live on Fusion HCM ...