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.

Redwood Recruiting: the ESS and OSCS index ingest jobs must all succeed, or the data won't load

# Fix of the Day #25: Redwood Recruiting: the ESS and OSCS index ingest jobs must all succeed, or the data won't load Part of my Oracl...