오라클 숫자체크 펑션 – function

오라클 숫자체크 펑션 – function

숫자이면 1, 아니면 0

========================================

FUNCTION FUN_IS_NUMBER ( V_STR VARCHAR2 )
RETURN VARCHAR2 IS RETURN_VALUE VARCHAR2(10);

BEGIN

IF ( LENGTH(TRIM(V_STR)) > 0 ) THEN
SELECT DECODE(ABS(SIGN(TRIM(V_STR))), 0, 1, 1)
INTO RETURN_VALUE
FROM DUAL;
ELSE
RETURN_VALUE := ‘0’;
END IF;

RETURN RETURN_VALUE ;

EXCEPTION
WHEN OTHERS THEN
RETURN ‘0’;
END;

========================================
SELECT
FUN_IS_NUMBER(‘199’)
, FUN_IS_NUMBER(‘-10 ‘)
, FUN_IS_NUMBER(‘ 0’)
, FUN_IS_NUMBER(22)
, FUN_IS_NUMBER(-99)
, FUN_IS_NUMBER(0)
, FUN_IS_NUMBER(‘ 123dddd444’)
, FUN_IS_NUMBER(”)
FROM DUAL;

FUN_IS_NUMBER(‘199’) 1
FUN_IS_NUMBER(‘-10’) 1
FUN_IS_NUMBER(‘0’) 1
FUN_IS_NUMBER(22) 1
FUN_IS_NUMBER(-99) 1
FUN_IS_NUMBER(0) 1
FUN_IS_NUMBER(‘123DDDD444’) 0
FUN_IS_NUMBER(”) 0

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.