행위

오라클 FN ENG TO NUMBER 함수

DB CAFE

thumb_up 추천메뉴 바로가기


CREATE OR REPLACE FUNCTION FN_ENG_TO_NUMBER ( inNumber NUMBER)

RETURN VARCHAR2

IS

v_str varchar(300)  := NULL;

v_number NUMBER  := NULL;

/*

* Author  : ym.k
* Purpose : 숫자 영문 표기
* Date    : 2016-10-24
*/

BEGIN

IF inNumber IS NULL OR inNumber = 0 THEN

   	RETURN 'ZERO';
 END IF;
 v_number := inNumber;
 IF inNumber < 0 THEN
   	v_number := inNumber * -1;
 END IF;


 SELECT CASE WHEN inNumber < 0 THEN 'Minus ' ELSE  END ||
        REPLACE(TRIM(
          DECODE(SUBSTR(v,1,3),0,, TO_CHAR(TO_DATE(SUBSTR(v,1,3),'j'),'Jsp Trillion '))
       || DECODE(SUBSTR(v,4,3),0,, TO_CHAR(TO_DATE(SUBSTR(v,4,3),'j'),'Jsp Billion '))
       || DECODE(SUBSTR(v,7,3),0,, TO_CHAR(TO_DATE(SUBSTR(v,7,3),'j'),'Jsp Million '))
       || DECODE(SUBSTR(v, 10),0,, TO_CHAR(TO_DATE(SUBSTR(v,10),'j'),'Jsp'))
       ),'-',' ') v
 INTO v_str
 FROM (SELECT LPAD(TRUNC(v_number),15,'0') v FROM DUAL);



 RETURN v_str;


EXCEPTION

   WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RETURN NULL;

END FN_ENG_TO_NUMBER; /