오라클 FN ENG TO NUMBER 함수
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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; /