FN . (Extract Numeric Value from a Column)

CREATE OR REPLACE FUNCTION GET_NUMBER
(NM VARCHAR2)
RETURN
VARCHAR2
IS
ST NUMBER(4) := 0;
EN NUMBER(4) := 0;
BEGIN
IF(nm IS NOT NULL) THEN

FOR i IN 1..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) >48 AND ASCII(SUBSTR(nm,i,1)) <57THEN
st:=i;
EXIT;
END IF;
END LOOP;

FOR i IN st..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) <48 THEN
EN:=i;
EXIT;
END IF;
END LOOP;
IF(st!=0) THEN
RETURN (SUBSTR(nm,st,EN-st));
ELSE
RETURN ' ';
END IF;
ELSE
RETURN ' ';
END IF;

END;
/