이번 퀴즈로 배워보는 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] 단계별 처리 과정

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

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