퀴즈로 배우는 SQL
[퀴즈] 숫자를 한글로 변환하기 1 9 13,022

by 마농 숫자를한글로 DECODE LPAD TRANSLATE REGEXP_REPLACE [2012.11.26]


이번 퀴즈로 배워보는 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
;

  • [표 1] 원본 테이블
            AMT
---------------
123456789012345
          29000
         309840

  • [표 2] 결과 테이블
            AMT V
--------------- --------------------------------------------------------
123456789012345 일백이십삼조사천오백육십칠억팔천구백일만이천삼백사십오
          29000 이만구천
         309840 삼십만구천팔백사십

문제설명

이 문제는 아라비아 숫자로 표시된 금액을 한글로 변환하여 보여주는 문제입니다. 우리가 은행에서 자주 접하게 되는 상황입니다. 금액은 최대 16자리까지입니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트2] 정답 리스트
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)
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이 문제를 풀기 위해서는 숫자를 한글로 표현하는 규칙부터 파악 하는 것이 우선이겠지요.

숫자에 자리수 표시할 때 3자리마다 컴마를 찍어 표현합니다만, 이는 영어로 표현할 때 편리하기 위함입니다.

우리말로 수를 표현할때는 4자리마다 컴마를 찍는것이 편리합니다. 예를 들면 12,3456 이란 수는 일십이만,삼천사백오십육(1십2만,3천4백5십6)으로 읽습니다.

숫자 사이사이에 단위가 표현이 되며, 4자리씩 끊어 천백십~이 반복적으로 표현되고, 4자리씩 늘어날 때마다 만,억,조,... 식의 4자리 기준 단위가 표현됩니다.자 그럼 이 특성을 이용하여 문제를 풀어보겠습니다.

우선 4자리씩 4번 반복하면 최대 표현하고자 하는 16자리 수까지 표현이 가능합니다. 주어진 수는 16자리 고정이 아니라 그보다 적은 수도 있으니 16자리를 꽉 채워보겠습니다.

  • [리스트 3] 숫자 자리수 고정
SELECT amt, LPAD(amt,16,'0') v FROM t;

  • [표 3] 숫자 자리수 고정
            AMT V                 
--------------- ------------------
123456789012345 0123456789012345  
          29000 0000000000029000  
         309840 0000000000309840  

이제 4자리씩 잘라보겠습니다.

  • [리스트 4] 4자리씩 분리
SELECT amt
     , SUBSTR(v, 1,4) 조
     , SUBSTR(v, 5,4) 억
     , SUBSTR(v, 9,4) 만
     , SUBSTR(v,13,4) 일
  FROM (SELECT amt, LPAD(amt,16,'0') v FROM t)
;

  • [표 4] 4자리씩 분리
            AMT 조         억         만         일       
--------------- ---------- ---------- ---------- ---------
123456789012345 0123       4567       8901       2345     
          29000 0000       0000       0002       9000     
         309840 0000       0000       0030       9840     

각 4자리수마다 "천백십"과 "조억만"의 단위를 붙여보도록 하겠습니다.

  • [리스트 5] 전체 단위 표현
SELECT amt
     , SUBSTR(v, 1,1)||'천'
    || SUBSTR(v, 2,1)||'백'
    || SUBSTR(v, 3,1)||'십'
    || SUBSTR(v, 4,1)||'조'
    || SUBSTR(v, 5,1)||'천'
    || SUBSTR(v, 6,1)||'백'
    || SUBSTR(v, 7,1)||'십'
    || SUBSTR(v, 8,1)||'억'
    || SUBSTR(v, 9,1)||'천'
    || SUBSTR(v,10,1)||'백'
    || SUBSTR(v,11,1)||'십'
    || SUBSTR(v,12,1)||'만'
    || SUBSTR(v,13,1)||'천'
    || SUBSTR(v,14,1)||'백'
    || SUBSTR(v,15,1)||'십'
    || SUBSTR(v,16,1) v
  FROM (SELECT amt, LPAD(amt,16,'0') v FROM t)
;
  • [표 5] 전체 단위 표현
            AMT V                                                
--------------- -------------------------------------------------
123456789012345 0천1백2십3조4천5백6십7억8천9백0십1만2천3백4십5   
          29000 0천0백0십0조0천0백0십0억0천0백0십2만9천0백0십0   
         309840 0천0백0십0조0천0백0십0억0천0백3십0만9천8백4십0   

자 이제 단위를 모두 붙였으니 불필요하게 붙은 단위를 제거해야 겠습니다. "0천", "0백", "0십" 처럼 0 뒤에는 천백십이 붙을 필요가 없습니다.

[표 5]은 3번째 행 결과를 보면 "0만"의 "만"은 지워져서는 안됩니다.

하지만 억단위의 "0억"에서 "억"은 지워져야 합니다. 어떤 차이가 있을까요? 만, 억, 조 등의 단위에서는 바로 앞 0 만 볼게 아니라 4자리가 모두 0인지를 확인해서 단위가 필요한지를 파악해야 합니다.

즉, "0천0백0십0억"처럼 4자리 모두 0일때만 "억"단위가 지워져야 하는 것입니다. 이를 정리해보면 "조억만"등의 단위는 해당 4자리가 모두0일때 지워져야 하고, 기타 "천백십"의 단위는 바로 앞숫자가 0일때 지워지면 됩니다.

  • [리스트 6] 불필요한 단위 삭제
SELECT amt
     , 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) v
  FROM (SELECT amt, LPAD(amt,16,'0') v FROM t)
;
  • [표 6] 불필요한 단위 삭제
            AMT V                                                
--------------- -------------------------------------------------
123456789012345 01백2십3조4천5백6십7억8천9백01만2천3백4십5 
          29000 000000000002만9천000                       
         309840 00000000003십0만9천8백4십0 

[리스트 6]의 쿼리에서는 숫자 한자리가 0일 경우 천백십을 표현하지 않고 공백을 붙이고, 조억만의 단위에서는 4자리를 잘라 0과 비교하여 단위를 표현했습니다.

이제 [결과 6]에서 숫자 0을 지우고 "123456789"를 "일이삼사오육칠팔구"로 바꿔주면 정답이 완성됩니다. 이는 TRANSLATE 함수를 이용하면 간단하게 해결됩니다. [리스트 2]의 정답 쿼리 완성.

LPAD, SUBSTR, DECODE, TRANSLATE, || 등의 기본적인 문자 함수와 비교함수를 이용하여 문제를 풀어보았습니다. 하지만 반복되는 SUBSTR 과 DECODE 가 눈에 거슬리네요.

다른 방법이 없을까요? 4자리의 패턴이 반복적으로 나타나고 있는데요. 문자열의 패턴을 이용하는 방법인 정규식을 이용하면 어떨까요?

이번에는 정규식으로 문제풀이에 도전해 보도록 하겠습니다. 우선 정답쿼리부터 살펴보고 쿼리에 대한 해설을해보도록 하겠습니다.

  • [리스트 7] 정규식을 이용한 해법
SELECT amt
     , TRANSLATE(
       REGEXP_REPLACE(
       REGEXP_REPLACE(
       REGEXP_REPLACE(
       LPAD(amt,16,'0')
       , '(.)(.)(.)(.)', '\1천\2백\3십\4 ')
       , '(.*) (.*) (.*) (.*) ', '\1조\2억\3만\4')
       , '0천0백0십0.|0[천백십]', '')
       , '1234567890', '일이삼사오육칠팔구') v
  FROM t
;

쿼리가 좀더 간결해 지긴 했지만 알아보기 힘든 기호들로 가득하네요.

여러 가지 함수들이 중첩으로 사용되었습니다. 제일 안쪽 괄호부터 단계별로 문자열이 변환 되어 나가는 과정을 차근차근 풀어보겠습니다.

  • [표 7] 단계별 처리 과정
단계 수식 변환값
AMT AMT 309840
V1 LPAD(amt, 16, '0') 0000000000309840
V2 REGEXP_REPLACE(v1, '(.)(.)(.)(.)', '\1천\2백\3십\4 ') 0천0백0십0 0천0백0십0 0천0백3십0 9천8백4십0
V3 REGEXP_REPLACE(v2, '(.*) (.*) (.*) (.*) ', '\1조\2억\3만\4') 0천0백0십0조0천0백0십0억0천0백3십0만9천8백4십0
V4 REGEXP_REPLACE(v3, '0천0백0십0.|0[천백십]', '') 3십0만9천8백4십0
V5 TRANSLATE(v4, '1234567890', '일이삼사오육칠팔구') 삼십만구천팔백사십

1단계는 LPAD를 이용해 16자리로 자리수를 꽉 채우는 단계입니다.

2단계는 정규식을 이용해 문자열을 변경했습니다. REGEXP_REPLACE(원본, 패턴, 변환값)은 문자의 패턴을 찾아 변환하는 함수입니다. 여기서 점(.)은 모든 문자를 의미합니다. 괄호는 서브패턴을 의미한다고 보시면 됩니다.

변환값에 나오는 \1 은 앞서 말한 서브패턴 중 첫 번째를 의미합니다. 앞의 '(.)(.)(.)(.)'은 4자리문자를 의미하며 괄호를 써서 4개의 서브패턴으로 구분지었습니다. 뒤의 천 백 십 은 각 서브패턴을 '\1 \2 \3 \4 ' 그대로 유지한채 사이사이에 '천백십 '을 넣으라는 의미로 해석하시면 됩니다.

3단계는 2단계의 결과에 다시 정규식을 적용해 변환하였습니다.

(.*)에서 * 는 (.)이 여러개 연결되어 있음을 의미합니다. 즉, '(.*) (.*) (.*) (.*) '은 문자열 사이사이에 공백이 있는 패턴을 의미합니다.

즉, 공백으로 구별되는 4개의 서브패턴을 가진 문자열 V2 와 일치합니다. '\1조\2억\3만\4' 은 각 4가지 서브패턴값의 사이에 '조억만'을 넣으라는 의미입니다.

4단계는 불필요하게 추가된 0에 대한 단위를 삭제하는 과정입니다.

'0천0백0십0.'은 0천0백0십0에 모든문자를 나타내는 점이 붙은 형태로 조억만 단위의 앞4 자리수가 모두 0일때를 의미합니다. '|' 이 기호는 OR 의 의미를 가지고 있으며 '0[천백십]'은 0으로 시작해서 천 또는 백 또는 십으로 끝나는 패턴입니다.

대괄호에 나열된 문자는 연결된 문자를 의미하는 것이 아니라 각각의 문자가 따로 인지된다 고 보시면 됩니다. 결국 '0천0백0십0.|0[천백십]'의 전체적인 의미는 0천0백0십. 이거나 0 천 이거나 0백 이거나 0십 인 모든 패턴을 의미하며 마지막으로 이런 패턴들을 모두 공백 으로 바꾸라는 의미가 됩니다. 즉, 쓸모없는 패턴문자열을 삭제하는 구문입니다.

마지막 5단계는 숫자를 한글로 치환하는 과정입니다.

앞선 정규식 이전의 문제풀이에서 TRANSLATE 함수에 대한 설명이 부족했던 관계로 여기 서 저세히 설명드리겠습니다.

TRANSLATE 는 원본 문자에서 두 번째 인자의 문자열을 한글자씩 잘랐을때 이에 매치되 는 문자가 있을 경우, 세 번째 인자의 문자열의 같은 자리에 해당하는 한글자로 치환하는 함수입니다.

TRANSLATE(v4, '1234567890', '일이삼사오육칠팔구')은 V4 값을 변환하는데 '1'은 '일'로, '2'는 '이'로, ..., 9는 구로 바꾸고 0의 경우엔 3번째 인자에 해당 자리수에 해 당하는 문자가 없으므로 공백으로 바뀌게 되는 것입니다.

어떤가요? 어려운가요?

[리스트 2]의 정답 쿼리 에서는 비교적 간단한 함수들을 사용하여 문제를 풀어서, 비록 쿼 리는 반복되는 패턴으로 조금 길어졌지만 이해하기엔 더 쉬운 편이구요.

[리스트 7]의 정답 쿼리 에서는 정규식을 사용하여 문제를 풀어서, 비록 쿼리는 간결해졌 지만, 정규식의 기호들을 이해하기엔 시간과 노력이 더 필요할 듯 하네요.

이와는 다른, 좀 더 기발한 방법은 없는지? 여러분이 한번 찾아보시길 바랍니다.

- 강좌 URL : http://www.gurubee.net/lecture/2224

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 여비몬 [2013.02.18 10:27:03]

좋은자료 감사합니다.

by 장비 [2013.05.07 08:50:34]
select * from v$version;

Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

인데요..

select translate( '1234567890', '1234567890','일이삼사오육칠팔구')  from dual 의 결과가
일이삼사오
여기 까지만 나옵니다.
그래서 제가 나름 할때는 decode로 손좀 더 갔네요
숫자 1바이트 한글 2바이트여서 그런거 같은데...
먼가 옵션이 필요한가요 ?

by 마농 [2013.05.07 09:22:30]
케릭터셋 확인이 필요할 듯 하네요.
한글을 공식적으로 지원하는 케릭터셋이 아닐 듯 하네요. US7ASCII
글자 하나당 하나씩 매칭이 되어야 하는데
'12' 가 '일' 로 매칭된것 같습니다.
MSWIN949 나 KSC5601 에서는 다음과 같은 결과가 나오는데
LENGTH('일') = 1
LENGTHB('일') = 2
US7ASCII 에서는 2가 나올 것입니다. 아마도.
LENGTH('일') = 2
한글 1글자를 1글자로 안보고 2글자로 인식하는거죠.

by 장비 [2013.05.07 15:44:04]

일반적으로 캐릭터셋이 설정되 있는 상태에선 문제 없는거군요 ..
감사합니다.
덕분에 많이 알아가네요 ㅎㅎ

by 강정식 [2014.11.06 14:03:29]

저는 다른 방식으로 접근해 보았습니다. LISTAGG() 함수를 이용해서 11g 이상 가능합니다.

WITH TAB AS (
    SELECT 1 GUBN, 210389012380128390128 VAL FROM DUAL UNION ALL
    SELECT 2 GUBN, 2342321               VAL FROM DUAL UNION ALL
    SELECT 3 GUBN, 43545634432           VAL FROM DUAL UNION ALL
    SELECT 4 GUBN, 4352436254625426456   VAL FROM DUAL     
)
SELECT MAX(KR_CONV) KO_CONV
FROM   (SELECT GUBN, 
               LISTAGG(KO_GOAL, '') WITHIN GROUP (ORDER BY CNT_1) OVER(PARTITION BY GUBN) AS KR_CONV
        FROM   (SELECT T.GUBN,
                       A.CNT_1,
                       KO_LAN_1 || 
                       CASE WHEN KO_LAN_1 IS NULL THEN NULL ELSE KO_LAN_2 END || 
                       DECODE(LENGTH(T.VAL) - CNT_1 + 1, 5,  '만 ',
                                                         9,  '억 ',
                                                         13, '조 ',
                                                         17, '경 ',
                                                         21, '해 ',
                                                         25, '자 ',
                                                         29, '양 ',
                                                         33, '구 ',
                                                         37, '간 ',
                                                         41, '정 ',
                                                         45, '재 ',
                                                         49, '극 ') KO_GOAL
                FROM   -- 대상 숫자
                       TAB T,
                       -- 가상 집합
                       (SELECT LEVEL CNT_1 
                        FROM   DUAL 
                        CONNECT BY LEVEL <= 100
                       ) A,
                       -- 한자리 한글로 변환 집합
                       (SELECT LEVEL - 1 CNT_2, 
                               DECODE(LEVEL - 1, 0, '',
                                                 1, '일',
                                                 2, '이',
                                                 3, '삼',
                                                 4, '사',
                                                 5, '오',
                                                 6, '육',
                                                 7, '칠',
                                                 8, '팔',
                                                 9, '구') KO_LAN_1
                         FROM DUAL CONNECT BY LEVEL <= 10
                        ) B,
                       -- 4자리 한글로 변환 집합
                       (SELECT DECODE(LEVEL, 4, 0, LEVEL) CNT_3,
                               DECODE(LEVEL, 2, '십',
                                             3, '백',
                                             4, '천') KO_LAN_2 
                        FROM   DUAL
                        CONNECT BY LEVEL <= 4) C
                WHERE  LENGTH(T.VAL)                    >= A.CNT_1
                AND    SUBSTR(T.VAL, CNT_1, 1)          =  B.CNT_2
                AND    MOD(LENGTH(T.VAL) - CNT_1 + 1, 4) = C.CNT_3
                ORDER BY A.CNT_1
                )
        )
GROUP BY GUBN
ORDER BY GUBN
;
 
KR_CONV
--------------------------------------------------------------------------------
이해 일천삼십팔경 구천일십이조 삼천팔백일억 이천팔백삼십구만 일백이십팔
이백삼십사만 이천삼백이십일
사백삼십오억 사천오백육십삼만 사천사백삼십이
사백삼십오경 이천사백삼십육조 이천오백사십육억 이천오백사십이만 육천사백오십육
;

 


by 마농 [2014.11.06 15:20:22]

오타 발견~~ 조가 두번 나오네 ^^


by 강정식 [2014.11.06 17:17:34]

하이 형 ㅎㅎ 스터디는 잼있으시져? ^^

오랜만에 형님 문제가 있어서 한번 풀어봤어요 ㅋ

근데 조가 두번 나오나요? 혹시 숫자 4개 중 1, 4 번에 있는 조를 보고 말씀하신건가여?


by 마농 [2014.11.06 17:23:01]

Line 18

- 오류 : 만억조경해양구간정재극
- 수정 : 만억조경해양구간정재극


by 강정식 [2014.11.06 17:34:18]

아항 ^^ 오타가 있었군여.. 지금 수정할께요~ 땡큐 형

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입