지난 "숫자를 한글로 변환하기" 퀴즈에서는 숫자를 한글로 바꾸는 문제에 대해 다루었었습니다
퀴즈로 배워보는 SQL 시간에는 지난시간에 이어 숫자를 영문으로 바꾸는 문제를 풀어보도록 하겠습니다. 지면 특성상 문제와 정답 그리고 해설이 같이 있습니다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한 후 정답과 해설을 참조하길 바랍니다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
숫자를 영문으로 변환하는 쿼리를 작성하세요
- [리스트 1] 원본 리스트
CREATE TABLE T
AS
(
SELECT 123456789012345 amt FROM dual
UNION ALL SELECT 29000 FROM dual
UNION ALL SELECT 309840 FROM dual
)
SELECT * FROM t;
AMT
----------------
123456789012345
29000
309840
-- SQL*PLUS에서 1.2346E+14로 조회 될 경우 FORMAT 설정
COL AMT FORMAT 999999999999999
- [표 2] 결과 테이블
| AMT | V |
|---|---|
| 123456789012345 | One Hundred Twenty-Three Trillion Four Hundred Fifty-Six Billion Seven Hundred Eighty-Nine Million Twelve Thousand Three Hundred Forty-Five |
| 29000 | Twenty-Nine Thousand |
| 309840 | Thirty-Nine Thousand Eight Hundred Forty |
문제설명
이 문제는 아라비아 숫자로 표시된 금액을 영문으로 변환하여 보여주는 문제입니다. 금액은 최대 15자리까지입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT amt
, 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
FROM (SELECT amt, LPAD(amt,15,'0') v FROM t)
;
AMT V
---------------- -------------------------------------------------
123456789012345 One Hundred Twenty-Three Trillion Four Hundred
Fifty-Six Billion Seven Hundred Eighty-Nine Million
Twelve Thousand Three Hundred Forty-Five
29000 Twenty-Nine Thousand
309840 Three Hundred Nine Thousand Eight Hundred Forty
어떤가요? 여러분이 만들어본 리스트와 같은가요?
틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
지난시간 숫자를 한글로 바꿀때는 숫자 4자리마다 단위(만, 억, 조)가 바뀌는 성질을 이용해 문제를 풀었습니다. 한글 숫자의 특징은 사용되는 글자가 한정되어 있다는 것이죠.
"일이삼사오육칠팔구십백천만억조" 이렇게 15개 글자로 모든 수의 표현이 가능했습니다. 참고로 지난시간 정답 살펴보겠습니다.
- [리스트 3] 숫자를 한글로
SELECT amt
, TRANSLATE
( SUBSTR(v, 1,1)||DECODE(SUBSTR(v, 1,1),0,'','천')
|| SUBSTR(v, 2,1)||DECODE(SUBSTR(v, 2,1),0,'','백')
|| SUBSTR(v, 3,1)||DECODE(SUBSTR(v, 3,1),0,'','십')
|| SUBSTR(v, 4,1)||DECODE(SUBSTR(v, 1,4),0,'','조')
|| SUBSTR(v, 5,1)||DECODE(SUBSTR(v, 5,1),0,'','천')
|| SUBSTR(v, 6,1)||DECODE(SUBSTR(v, 6,1),0,'','백')
|| SUBSTR(v, 7,1)||DECODE(SUBSTR(v, 7,1),0,'','십')
|| SUBSTR(v, 8,1)||DECODE(SUBSTR(v, 5,4),0,'','억')
|| SUBSTR(v, 9,1)||DECODE(SUBSTR(v, 9,1),0,'','천')
|| SUBSTR(v,10,1)||DECODE(SUBSTR(v,10,1),0,'','백')
|| SUBSTR(v,11,1)||DECODE(SUBSTR(v,11,1),0,'','십')
|| SUBSTR(v,12,1)||DECODE(SUBSTR(v, 9,4),0,'','만')
|| SUBSTR(v,13,1)||DECODE(SUBSTR(v,13,1),0,'','천')
|| SUBSTR(v,14,1)||DECODE(SUBSTR(v,14,1),0,'','백')
|| SUBSTR(v,15,1)||DECODE(SUBSTR(v,15,1),0,'','십')
|| SUBSTR(v,16,1)
, '1234567890', '일이삼사오육칠팔구') v
FROM (SELECT amt, LPAD(amt,16,'0') v FROM t)
;
그러나, 15개 글자만 조합해서 나오던 한글과 달리 영문은 꽤나 복잡해 보이고, 해결의 실마리를 찾지 못할 수 있습니다. 영어로 된 숫자를 만들어 주는 함수는 없을까요?
다음[리스트 4]의 날짜변환함수를 살펴보도록 하겠습니다.
- [리스트 4] 날짜변환함수
SELECT TO_CHAR(sysdate, 'yyyy') yyyy
, TO_CHAR(sysdate, 'mm') mm
, TO_CHAR(sysdate, 'dd') dd
FROM DUAL
UNION ALL
SELECT TO_CHAR(sysdate, 'yyyysp') yyyy
, TO_CHAR(sysdate, 'mmsp') mm
, TO_CHAR(sysdate, 'ddsp') dd
FROM DUAL
;
YYYY MM DD
------------------------------ ------------------ ----------------
2013 10 23
two thousand thirteen ten twenty-three
<리스트 4>의 결과를 보시면 각각의 날짜 포맷에 'SP' 를 뒤에 붙였더니 숫자가 영문으로 바뀌어 표시되는 것을 확인 할수 있습니다.
그렇다면 연,월,일을 나타내는 포맷 외에 좀 더 큰 수를 표현할 수 있는 날짜포맷은 없을까? 이 날짜포멧에 'SP'를 붙인다면 위 문제가 해결될 것 같습니다. 'SP' 는 Spelled Number 로 영문 표기 숫자로 보시면 되겠습니다.
다음 <리스트 5>의 날짜변환함수를 살펴보도록 하겠습니다.
- [리스트 5] Julian Date
SELECT TO_CHAR(sysdate, 'J') J
, TO_CHAR(sysdate, 'Jsp') Jsp
FROM dual
;
J JSP
-------- --------------------------------------------------------------------
2456589 Two Million Four Hundred Fifty-Six Thousand Five Hundred Eighty-Nine
<리스트 5> 의 결과를 보면 sysdate 날짜가 'J' 라는 날짜포멧으로 변환되면서 숫자형태를 나타내고 있습니다. J 는 Julian Date 의 포맷으로 날짜를 숫자형태로 표현하고 있습니다.
'J'에 'sp' 를 붙이니 이 숫자가 영문으로 변환이 됩니다.
그렇다면 우리가 변환하기를 원하는 수를 Julian Date 의 포맷으로 날짜로 변환한 뒤 이 날짜를 다시 'JSP' (Julian Date 포맷 +Spelled Number)로 바꾼다면 쉽게 문제가 해결될 것같습니다.
- [리스트 6] Jsp 를 이용한 숫자 영문 변환
SELECT 29000 amt
, TO_CHAR(TO_DATE(29000, 'J'), 'bc yyyy.mm.dd') j_date
, TO_CHAR(TO_DATE(29000, 'J'), 'Jsp') jsp
FROM dual
;
AMT J_DATE JSP
------- -------------- -----------------------
29000 bc 4633.05.26 Twenty-Nine Thousand
<리스트 6> 의 결과를 보면 성공적으로 변환이 된 것을 확인 할 수 있습니다. 변환 중간과정의 날짜는 좀 엉뚱한 값이긴 하지만 이 값이 필요한것은 아니고, 최종 영문 표현결과가 정상으로 나왔으니, 이제 이 방법을 원본테이블에 적용하면 될까요?
- [리스트 7] Jsp 변환 오류
SELECT TO_CHAR(TO_DATE(0, 'J'), 'Jsp') jsp FROM dual; 오류 보고: SQL 오류: ORA-01854: 율리우스 날짜는 1에서 5373484 사이여야 합니다 01854. 00000 - "julian date must be between 1 and 5373484" *Cause: An invalid Julian date was entered. *Action: Enter a valid Julian date between 1 and 5373484.
<리스트 7>을 보면 숫자 0 을 대입시켜 봤더니 에러가 났습니다. 영문으로 변환 가능한 수는 1에서 5373484 사이여야 하네요. 최대 15자리까지 표현해야 하는 상항에서는 사용할 수 없는 방법입니다.
- [표] 영문 숫자의 특징 분석
| AMT | V |
|---|---|
| 123,456,789,012,345 | One Hundred Twenty-Three Trillion Four Hundred Fifty-Six Billion Seven Hundred Eighty-Nine Million Twelve Thousand Three Hundred Forty-Five |
위 표는 가장 큰 수를 가지고 그 특징을 분석하기 위해 조금 다르게 표현해 봤습니다.
한글표현 수에서는 4자리마나 단위가 바뀌었습니다. (만,억,조) 하지만 4자리씩 끊어서 봤을때는 똑같은 패턴을 보입니다.(천백십일)
영문표현 수도 한글과 마찬가지입니다. 다만 4자리가 아닌 3자리씩 끊는다는 차이가 있지요. 즉, 3자리씩 끊어서 단위가 바뀌고(Trillion, Billion, Million, Thousand ), 마찬가지로 3자리씩은 동일한 표현방법을 하고 있습니다.
이 특징을 이용하여 3자리씩 잘라서 Jsp 를 이용해 영문으로 바꾸고 그 사이사이에 (Trillion, Billion, Million, Thousand )등을 끼워 넣어준다면 큰 수도 처리가 가능하게 됩니다.
리스트 의 정답 <리스트 2> 를 구문별로 정리해 보겠습니다.
우선 LPAD 를 이용해 최대 허용 자리수인 15자리를 0 으로 채웁니다. 이렇게 나온 값을 3자리씩 잘라 Jsp 포맷을 이용하여 영문으로 변경하고 해당 숫의 단위를 삽입합니다.
TO_CHAR(TO_DATE(SUBSTR(v,1,3),'j'),'Jsp "Trillion" ') TO_CHAR(TO_DATE(SUBSTR(v,4,3),'j'),'Jsp "Billion" ') TO_CHAR(TO_DATE(SUBSTR(v,7,3),'j'),'Jsp "Million" ')
이때 3자리가 모두 0인 경우엔 해당 단위를 붙여줄 필요가 없으므로 DECODE를 이용하여 ‘0’ 일때는 단위를 붙이지 않습니다.
DECODE(SUBSTR(v,1,3),0,''
, TO_CHAR(TO_DATE(SUBSTR(v,1,3),'j'),'Jsp "Trillion" '))
마지막 맨 뒤 6자리는 Julian Date의 한계값인 5373484 보다 작은 값이므로 굳이 3자리씩 잘라서 두 번 처리하지 않아도 되기 때문에 한번에 처리합니다.
DECODE(SUBSTR(v, 10),0,''
, TO_CHAR(TO_DATE(SUBSTR(v,10),'j'),'Jsp'))
마지막 TRIM 함수로 앞 뒤 공백을 제거하고 원하는 결과를 얻었습니다.
Julian Date 라는 날짜 표현방식과 숫자를 영문으로 표기해주는 'SP' 포맷을 이용하여 숫자를 영문으로 쉽게 변경하여 표기가 가능하게 되었습니다. 또한 Julian Date 표현의 한계를 극복하는 방법도 함께 살펴보았습니다.
보너스 문제입니다. 이렇게 바꾼 영문 숫자를 다시 원래대로 바꿔볼까요?
- [보너스] 영문 표현 수를 숫자로 변환
WITH t AS
(
SELECT 'One Hundred Twenty-Three Trillion Four Hundred Fifty-Six Billion Seven Hundred Eighty-Nine Million Twelve Thousand Three Hundred Forty-Five' amt FROM dual
UNION ALL SELECT 'Twenty-Nine Thousand' FROM dual
UNION ALL SELECT 'Thirty-Nine Thousand Eight Hundred Forty' FROM dual
)
SELECT amt
, ( NVL(REGEXP_SUBSTR(v1, '[^ -]+', 1, 1), 0)
+ NVL(REGEXP_SUBSTR(v1, '[^ -]+', 1, 2), 0)
+ NVL(REGEXP_SUBSTR(v1, '[^ -]+', 1, 3), 0) ) * 1E12
+ ( NVL(REGEXP_SUBSTR(v2, '[^ -]+', 1, 1), 0)
+ NVL(REGEXP_SUBSTR(v2, '[^ -]+', 1, 2), 0)
+ NVL(REGEXP_SUBSTR(v2, '[^ -]+', 1, 3), 0) ) * 1E9
+ ( NVL(REGEXP_SUBSTR(v3, '[^ -]+', 1, 1), 0)
+ NVL(REGEXP_SUBSTR(v3, '[^ -]+', 1, 2), 0)
+ NVL(REGEXP_SUBSTR(v3, '[^ -]+', 1, 3), 0) ) * 1E6
+ ( NVL(REGEXP_SUBSTR(v4, '[^ -]+', 1, 1), 0)
+ NVL(REGEXP_SUBSTR(v4, '[^ -]+', 1, 2), 0)
+ NVL(REGEXP_SUBSTR(v4, '[^ -]+', 1, 3), 0) ) * 1E3
+ ( NVL(REGEXP_SUBSTR(v5, '[^ -]+', 1, 1), 0)
+ NVL(REGEXP_SUBSTR(v5, '[^ -]+', 1, 2), 0)
+ NVL(REGEXP_SUBSTR(v5, '[^ -]+', 1, 3), 0) ) v
FROM (SELECT amt
, REGEXP_REPLACE(v, '(.+)Trillion|.', '\1') v1
, REGEXP_REPLACE(v, '(.+Trillion)?(.+)Billion|.', '\2') v2
, REGEXP_REPLACE(v, '(.+(Trillion|Billion))?(.+)Million|.', '\3') v3
, REGEXP_REPLACE(v, '(.+(Trillion|Billion|Million))?(.+)Thousand|.', '\3') v4
, REGEXP_REPLACE(v, '.+(Trillion|Billion|Million|Thousand)') v5
FROM (SELECT amt,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(amt
, 'Hundred' , '00')
, 'Ninety' , '90')
, 'Eighty' , '80')
, 'Seventy' , '70')
, 'Sixty' , '60')
, 'Fifty' , '50')
, 'Forty' , '40')
, 'Thirty' , '30')
, 'Twenty' , '20')
, 'Nineteen' , '19')
, 'Eighteen' , '18')
, 'Seventeen', '17')
, 'Sixteen' , '16')
, 'Fifteen' , '15')
, 'Fourteen' , '14')
, 'Thirteen' , '13')
, 'Twelve' , '12')
, 'Eleven' , '11')
, 'Ten' , '10')
, 'Nine' , '9')
, 'Eight' , '8')
, 'Seven' , '7')
, 'Six' , '6')
, 'Five' , '5')
, 'Four' , '4')
, 'Three' , '3')
, 'Two' , '2')
, 'One' , '1') v
FROM t
)
)
;
- 강좌 URL : http://www.gurubee.net/lecture/2645
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
도와주세요.. ^^
변경후 : , REGEXP_REPLACE(v, '.+(Trillion|Billion|Million|Thousand)') v5
http://oracleclub.com/article/50424
강좌 내용도 수정했습니다. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.