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.

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...