데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 숫자를 한글로 표기
문제. 숫자를 한글로 표기하는 쿼리문을 작성하세요.
WITH t AS
(
SELECT 123456789012345 amt FROM dual
UNION ALL SELECT 29000 FROM dual
UNION ALL SELECT 39840 FROM dual
)
SELECT * FROM t
;
[결과]
| AMT | V |
| 123456789012345 | 일백이십삼조사천오백육십칠억팔천구백일만이천삼백사십오 |
| 29000 | 이만구천 |
| 39840 | 삼만구천팔백사십 |
[정답보기] <=== 트리플클릭
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)
;
[정답보기 - 정규식] <=== 트리플클릭
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
;
SELECT
REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
TRANSLATE(TO_CHAR(AMT, '0000000000000000'), '1234567890', '일이삼사오육칠팔구영')
, '(.)' , '\1 ' )
, '([^ ]) ([^ ]) ([^ ]) ([^ ])', '\1천\2백\3십\4')
, '(.*) (.*) (.*) (.*) (.*)'
, '\1조 \2억 \3만 \4 ')
, '(영.)', '')
, ' ', '')
FROM
t ;
제가 생각하던 답보다는 간결해 보이긴 합니다.
저도 정규식에 도전해 봐야 겠네요.
일단 제출하신 쿼리는 약간의 오류가 있네요.
300000 으로 테스트해보세요.
SELECT REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
TRANSLATE(TO_CHAR(AMT, '0000000000000000'), '1234567890', '일이삼사오육칠팔구영')
, '(.)' , '\1 ' )
,'([^ ]) ([^ ]) ([^ ]) ([^ ])', '\1천\2백\3십\4_')
,'(.*) (.*) (.*) (.*) (.*)' , '\1조\2억\3만\4')
, '(영.)', '_')
,'(____.)','')
,'_','')
FROM
t ;
(
SELECT 123456789012345 AMT FROM DUAL
UNION ALL SELECT 29000 FROM DUAL
UNION ALL SELECT 39840 FROM DUAL
)
SELECT AMT
,XMLAGG(XMLELEMENT("nm",AMT2||TMP) ORDER BY LV).EXTRACT('//text()').GetStringVal() AS V
FROM (SELECT AMT,LV
,TRIM(TRANSLATE(TO_CHAR(AMT1, '0'), '123456789', '일이삼사오육칠팔구')) AMT2
,DECODE(MOD(LV,4),1,'천',2,'백',3,'십',DECODE(CEIL(LV/4),1,'조',2,'억',3,'만')) TMP
FROM (SELECT A.AMT,B.LV
,SUBSTR(LPAD(A.AMT,16,'0'),B.LV,1) AMT1
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 16) B
)
WHERE TO_NUMBER(AMT1) > 0
)
GROUP BY AMT
어렵습니다....덜덜덜...
SELECT 123456789012345 AMT FROM DUAL
UNION ALL SELECT 29000 FROM DUAL
UNION ALL SELECT 39840 FROM DUAL
UNION ALL SELECT 300000 FROM DUAL
)
SELECT AMT
,XMLAGG(XMLELEMENT("nm",AMT2||TMP1||TMP2) ORDER BY LV).EXTRACT('//text()').GetStringVal() AS V
FROM (
SELECT AMT,LV
,TRIM(TRANSLATE(TO_CHAR(AMT1, '0'), '123456789', '일이삼사오육칠팔구')) AMT2
,DECODE(MOD(LV,4),1,'천',2,'백',3,'십') TMP1
,CASE WHEN MOD(LV,4) = MIN(MOD(LV,4)) OVER(PARTITION BY AMT,CEIL(LV/4))
THEN DECODE(CEIL(LV/4),1,'조',2,'억',3,'만')
ELSE ''
END TMP2
FROM (SELECT A.AMT,B.LV
,SUBSTR(LPAD(A.AMT,16,'0'),B.LV,1) AMT1
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 16) B
)
WHERE TO_NUMBER(AMT1) > 0
)
GROUP BY AMT
제가 0보다 큰건 제외 시켜 놓고 매 자리가 있다고 가정을 해버렸네요...;;
아옹... 수정했습니다.... ㅡㅅㅜ
SELECT 123456789012345 AMT FROM DUAL
UNION ALL SELECT 123456000002345 FROM DUAL
UNION ALL SELECT 300000 FROM DUAL
)
SELECT AMT
,XMLAGG(XMLELEMENT("nm",AMT2||TMP1||TMP2) ORDER BY LV).EXTRACT('//text()').GetStringVal() AS V
FROM (SELECT AMT,LV
,TRIM(TRANSLATE(TO_CHAR(AMT1, '0'), '123456789', '일이삼사오육칠팔구')) AMT2
,DECODE(MOD(LV,4),1,'천',2,'백',3,'십') TMP1
,CASE WHEN LV = FIRST_VALUE(LV) OVER(PARTITION BY AMT,CEIL(LV/4) ORDER BY LV DESC)
THEN DECODE(CEIL(LV/4),1,'조',2,'억',3,'만')
ELSE NULL
END TMP2
FROM (SELECT A.AMT,B.LV
,SUBSTR(LPAD(A.AMT,16,'0'),B.LV,1) AMT1
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 16) B
)
WHERE TO_NUMBER(AMT1) > 0
)
GROUP BY AMT
매번 감사히 풀고 있습니다..
SELECT 123456789012345 AMT FROM DUAL UNION ALL
SELECT 123456000002345 AMT FROM DUAL UNION ALL
SELECT 300000 AMT FROM DUAL
)
SELECT AMT
,XMLAGG(XMLELEMENT("nm",AMT2) ORDER BY LV).EXTRACT('//text()').GetStringVal() AS V
FROM (SELECT AMT,LV
,(SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(DECODE(SUBSTR(AMT1,LEVEL,1),'영',NULL,SUBSTR(AMT1,LEVEL,1)||DECODE(LEVEL,1,'천',2,'백',3,'십')),'/')),'/','')
FROM DUAL
CONNECT BY LEVEL <= 4)||DECODE(LV,1,'조',2,'억',3,'만') AS AMT2
FROM (SELECT A.AMT,B.LV
,TRIM(TRANSLATE(SUBSTR(LPAD(A.AMT,16,'0'),(B.LV*4)-3,4), '1234567890', '일이삼사오육칠팔구영')) AMT1
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) B
)
WHERE AMT1 != '영영영영'
)
GROUP BY AMT
원래 생각했던 답 외에 구경서님 답글 참고해서 정규식 답안도 올렸습니다.
v상이v님 방법도 좋은 방법입니다.
하지만 행복제후 다시 합치는 과정을 없앨수 있다면 없애는 것이 더 좋을 듯 합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.