데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 한글로 된 수를 아라비아숫자로 표기
문제. 한글로 된 수를 숫자로 표기하는 쿼리문을 작성하세요.
WITH t AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' amt FROM dual
UNION ALL SELECT '이만구천' FROM dual
UNION ALL SELECT '삼만구천팔백사십' FROM dual
)
SELECT * FROM t
;
[결과]
AMT
V
일백이십조사천오백육십칠억팔천구백일만이천삼백사십오
120456789012345
이만구천
29000
삼만구천팔백사십
39840
[정답보기] <== 트리플클릭
SELECT amt
, TO_NUMBER(
NVL(REGEXP_REPLACE(v1, '(.)천|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v1, '(.)백|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v1, '(.)십|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v1, '.[천백십]', ''),'0')
|| NVL(REGEXP_REPLACE(v2, '(.)천|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v2, '(.)백|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v2, '(.)십|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v2, '.[천백십]', ''),'0')
|| NVL(REGEXP_REPLACE(v3, '(.)천|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v3, '(.)백|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v3, '(.)십|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v3, '.[천백십]', ''),'0')
|| NVL(REGEXP_REPLACE(v4, '(.)천|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v4, '(.)백|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v4, '(.)십|.', '\1'),'0')
|| NVL(REGEXP_REPLACE(v4, '.[천백십]', ''),'0')
) v
FROM
(
SELECT amt
, REGEXP_REPLACE(v, '(.+)조|.', '\1') v1
, REGEXP_REPLACE(v, '(.+조)?(.+)억|.', '\2') v2
, REGEXP_REPLACE(v, '(.+[조억])?(.+)만|.', '\2') v3
, REGEXP_REPLACE(v, '.+[조억만]') v4
FROM (SELECT amt, TRANSLATE(amt,'일이삼사오육칠팔구','123456789') v FROM t)
)
;
by v상이v
[2010.09.29 10:12:44]
WITH T AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '이만구천' FROM DUAL
UNION ALL SELECT '삼만구천팔백사십' FROM DUAL
)
SELECT AMT
,NVL(SUM(TO_NUMBER(SUBSTR(STR1,1,1)) * DECODE(SUBSTR(STR1,2,1),'천',1000,'백',100,'십',10,1)) * 1000000000000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR2,1,1)) * DECODE(SUBSTR(STR2,2,1),'천',1000,'백',100,'십',10,1)) * 100000000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR3,1,1)) * DECODE(SUBSTR(STR3,2,1),'천',1000,'백',100,'십',10,1)) * 10000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR4,1,1)) * DECODE(SUBSTR(STR4,2,1),'천',1000,'백',100,'십',10,1)),0)
AS NUM
FROM (
SELECT AMT, LV
,SUBSTR(TRANSLATE(SUBSTR(AMT, 1,INSTR(AMT,'조') - 1) ,'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str1
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'조')+1,INSTR(AMT,'억') - INSTR(AMT,'조') - 1),'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str2
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'억')+1,INSTR(AMT,'만') - INSTR(AMT,'억') - 1),'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str3
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'만')+1) ,'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str4
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) B
)
GROUP BY AMT
ORDER BY 1
다른방법으로 다시 해볼꼐요...
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '이만구천' FROM DUAL
UNION ALL SELECT '삼만구천팔백사십' FROM DUAL
)
SELECT AMT
,NVL(SUM(TO_NUMBER(SUBSTR(STR1,1,1)) * DECODE(SUBSTR(STR1,2,1),'천',1000,'백',100,'십',10,1)) * 1000000000000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR2,1,1)) * DECODE(SUBSTR(STR2,2,1),'천',1000,'백',100,'십',10,1)) * 100000000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR3,1,1)) * DECODE(SUBSTR(STR3,2,1),'천',1000,'백',100,'십',10,1)) * 10000,0)
+NVL(SUM(TO_NUMBER(SUBSTR(STR4,1,1)) * DECODE(SUBSTR(STR4,2,1),'천',1000,'백',100,'십',10,1)),0)
AS NUM
FROM (
SELECT AMT, LV
,SUBSTR(TRANSLATE(SUBSTR(AMT, 1,INSTR(AMT,'조') - 1) ,'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str1
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'조')+1,INSTR(AMT,'억') - INSTR(AMT,'조') - 1),'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str2
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'억')+1,INSTR(AMT,'만') - INSTR(AMT,'억') - 1),'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str3
,SUBSTR(TRANSLATE(SUBSTR(AMT,INSTR(AMT,'만')+1) ,'일이삼사오육칠팔구','123456789'),(LV*2)-1,2) AS str4
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) B
)
GROUP BY AMT
ORDER BY 1
다른방법으로 다시 해볼꼐요...
by park1q
[2010.09.30 16:46:00]
호호..여기 들와서 처음 퀴즈 해보는데..와~정말 고수들 많네요..
마농님은 정규식을 원했던것 같은데 까작대다가 말았습니다..
정답이 기대 됩니다.*^^*
상이님은 이상하게..일만구천 하면 109000으로 나오네요..
WITH t AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' amt FROM dual
UNION ALL SELECT '이만구천' FROM dual
UNION ALL SELECT '삼만구천팔백사십' FROM dual
UNION ALL SELECT '구조일만구천오' FROM DUAL
)
select knum, sum(lunit*sunit*nvl(amt2,0))
from (SELECT knum,
decode(llvl,4,1000000000000,3,100000000,2,10000,1) lunit,
decode(s.lvl,4,1000,3,100,2,10,1) sunit,
amt,
case
when s.lvl=4 and instr(amt,'천')>0 then substr(amt,instr(amt,'천')-1,1)
when s.lvl=3 and instr(amt,'백')>0 then substr(amt,instr(amt,'백')-1,1)
when s.lvl=2 and instr(amt,'십')>0 then substr(amt,instr(amt,'십')-1,1)
when s.lvl=1 and translate(substr(amt,-1,1),'0123456789',' ') is null then substr(amt,-1,1)
else
null
end amt2
from (select knum, lvl llvl,
case
when lvl=4 then substr(txt,1,instr(txt,'조') - 1)
when lvl=3 then substr(txt,instr(txt,'조')+1,instr(txt,'억')-instr(txt,'조')-1)
when lvl=2 then substr(txt,instr(txt,'억')+1,instr(txt,'만')-instr(txt,'억')-1)
else
substr(txt,instr(txt,'만')+1)
end amt
from (SELECT lvl,amt knum, translate(amt,'일이삼사오육칠팔구',123456789) txt
FROM t,
(select level lvl from dual connect by level <= 4) l
order by txt,l.lvl desc
)
),
(select level lvl from dual connect by level <= 4) s
order by knum, lunit desc , sunit desc, amt
)
group by knum ;
마농님은 정규식을 원했던것 같은데 까작대다가 말았습니다..
정답이 기대 됩니다.*^^*
상이님은 이상하게..일만구천 하면 109000으로 나오네요..
WITH t AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' amt FROM dual
UNION ALL SELECT '이만구천' FROM dual
UNION ALL SELECT '삼만구천팔백사십' FROM dual
UNION ALL SELECT '구조일만구천오' FROM DUAL
)
select knum, sum(lunit*sunit*nvl(amt2,0))
from (SELECT knum,
decode(llvl,4,1000000000000,3,100000000,2,10000,1) lunit,
decode(s.lvl,4,1000,3,100,2,10,1) sunit,
amt,
case
when s.lvl=4 and instr(amt,'천')>0 then substr(amt,instr(amt,'천')-1,1)
when s.lvl=3 and instr(amt,'백')>0 then substr(amt,instr(amt,'백')-1,1)
when s.lvl=2 and instr(amt,'십')>0 then substr(amt,instr(amt,'십')-1,1)
when s.lvl=1 and translate(substr(amt,-1,1),'0123456789',' ') is null then substr(amt,-1,1)
else
null
end amt2
from (select knum, lvl llvl,
case
when lvl=4 then substr(txt,1,instr(txt,'조') - 1)
when lvl=3 then substr(txt,instr(txt,'조')+1,instr(txt,'억')-instr(txt,'조')-1)
when lvl=2 then substr(txt,instr(txt,'억')+1,instr(txt,'만')-instr(txt,'억')-1)
else
substr(txt,instr(txt,'만')+1)
end amt
from (SELECT lvl,amt knum, translate(amt,'일이삼사오육칠팔구',123456789) txt
FROM t,
(select level lvl from dual connect by level <= 4) l
order by txt,l.lvl desc
)
),
(select level lvl from dual connect by level <= 4) s
order by knum, lunit desc , sunit desc, amt
)
group by knum ;
by 마농
[2010.09.30 21:49:34]
두분 모두 잘 풀어주셨습니다.
저는 정규식을 이용해 자료를 잘랐으며,
행복제 후 다시 합치는 방식을 이용하지 않고 풀어봤습니다.
저는 정규식을 이용해 자료를 잘랐으며,
행복제 후 다시 합치는 방식을 이용하지 않고 풀어봤습니다.
by park1q
[2010.10.01 08:01:11]
와~~이넘의 정규식은 아무리 봐도..이해 될듯말듯..
암튼..대단하십니다.
암튼..대단하십니다.
by v상이v
[2010.10.01 10:31:12]
WITH T AS (
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '이만구천' FROM DUAL
UNION ALL SELECT '삼만구천팔백사십' FROM DUAL
)
SELECT AMT
,TO_NUMBER(
DECODE(INSTR(V1,'천'),0,'0',SUBSTR(V1,INSTR(V1,'천')-1,1))
||DECODE(INSTR(V1,'백'),0,'0',SUBSTR(V1,INSTR(V1,'백')-1,1))
||DECODE(INSTR(V1,'십'),0,'0',SUBSTR(V1,INSTR(V1,'십')-1,1))
||SUBSTR(V1,LENGTH(V1))
||DECODE(INSTR(V2,'천'),0,'0',SUBSTR(V2,INSTR(V2,'천')-1,1))
||DECODE(INSTR(V2,'백'),0,'0',SUBSTR(V2,INSTR(V2,'백')-1,1))
||DECODE(INSTR(V2,'십'),0,'0',SUBSTR(V2,INSTR(V2,'십')-1,1))
||SUBSTR(V2,LENGTH(V2))
||DECODE(INSTR(V3,'천'),0,'0',SUBSTR(V3,INSTR(V3,'천')-1,1))
||DECODE(INSTR(V3,'백'),0,'0',SUBSTR(V3,INSTR(V3,'백')-1,1))
||DECODE(INSTR(V3,'십'),0,'0',SUBSTR(V3,INSTR(V3,'십')-1,1))
||SUBSTR(V3,LENGTH(V3))
||DECODE(INSTR(V4,'천'),0,'0',SUBSTR(V4,INSTR(V4,'천')-1,1))
||DECODE(INSTR(V4,'백'),0,'0',SUBSTR(V4,INSTR(V4,'백')-1,1))
||DECODE(INSTR(V4,'십'),0,'0',SUBSTR(V4,INSTR(V4,'십')-1,1))
||SUBSTR(V4,LENGTH(V4))
) AS V
FROM (
SELECT AMT
,CASE WHEN MOD(LENGTH(V1),2) = 0 THEN V1||'0' ELSE V1 END V1
,CASE WHEN MOD(LENGTH(V2),2) = 0 THEN V2||'0' ELSE V2 END V2
,CASE WHEN MOD(LENGTH(V3),2) = 0 THEN V3||'0' ELSE V3 END V3
,CASE WHEN MOD(LENGTH(V4),2) = 0 THEN V4||'0' ELSE V4 END V4
FROM (
SELECT AMT
,SUBSTR(V,0,INSTR(V,'조')-1) V1
,SUBSTR(V,INSTR(V,'조')+1,INSTR(V,'억')-INSTR(V,'조')-1) V2
,SUBSTR(V,INSTR(V,'억')+1,INSTR(V,'만')-INSTR(V,'억')-1) V3
,SUBSTR(V,INSTR(V,'만')+1) V4
FROM (SELECT AMT,TRANSLATE(AMT ,'일이삼사오육칠팔구','123456789') AS V FROM T)
)
)
꾸준히 바보 같이 정규식을 이용하지 않고 행복제 없이 풀어봤습니다...
park1q -- 일만구천도 잘 되는데......;;
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '이만구천' FROM DUAL
UNION ALL SELECT '삼만구천팔백사십' FROM DUAL
)
SELECT AMT
,TO_NUMBER(
DECODE(INSTR(V1,'천'),0,'0',SUBSTR(V1,INSTR(V1,'천')-1,1))
||DECODE(INSTR(V1,'백'),0,'0',SUBSTR(V1,INSTR(V1,'백')-1,1))
||DECODE(INSTR(V1,'십'),0,'0',SUBSTR(V1,INSTR(V1,'십')-1,1))
||SUBSTR(V1,LENGTH(V1))
||DECODE(INSTR(V2,'천'),0,'0',SUBSTR(V2,INSTR(V2,'천')-1,1))
||DECODE(INSTR(V2,'백'),0,'0',SUBSTR(V2,INSTR(V2,'백')-1,1))
||DECODE(INSTR(V2,'십'),0,'0',SUBSTR(V2,INSTR(V2,'십')-1,1))
||SUBSTR(V2,LENGTH(V2))
||DECODE(INSTR(V3,'천'),0,'0',SUBSTR(V3,INSTR(V3,'천')-1,1))
||DECODE(INSTR(V3,'백'),0,'0',SUBSTR(V3,INSTR(V3,'백')-1,1))
||DECODE(INSTR(V3,'십'),0,'0',SUBSTR(V3,INSTR(V3,'십')-1,1))
||SUBSTR(V3,LENGTH(V3))
||DECODE(INSTR(V4,'천'),0,'0',SUBSTR(V4,INSTR(V4,'천')-1,1))
||DECODE(INSTR(V4,'백'),0,'0',SUBSTR(V4,INSTR(V4,'백')-1,1))
||DECODE(INSTR(V4,'십'),0,'0',SUBSTR(V4,INSTR(V4,'십')-1,1))
||SUBSTR(V4,LENGTH(V4))
) AS V
FROM (
SELECT AMT
,CASE WHEN MOD(LENGTH(V1),2) = 0 THEN V1||'0' ELSE V1 END V1
,CASE WHEN MOD(LENGTH(V2),2) = 0 THEN V2||'0' ELSE V2 END V2
,CASE WHEN MOD(LENGTH(V3),2) = 0 THEN V3||'0' ELSE V3 END V3
,CASE WHEN MOD(LENGTH(V4),2) = 0 THEN V4||'0' ELSE V4 END V4
FROM (
SELECT AMT
,SUBSTR(V,0,INSTR(V,'조')-1) V1
,SUBSTR(V,INSTR(V,'조')+1,INSTR(V,'억')-INSTR(V,'조')-1) V2
,SUBSTR(V,INSTR(V,'억')+1,INSTR(V,'만')-INSTR(V,'억')-1) V3
,SUBSTR(V,INSTR(V,'만')+1) V4
FROM (SELECT AMT,TRANSLATE(AMT ,'일이삼사오육칠팔구','123456789') AS V FROM T)
)
)
꾸준히 바보 같이 정규식을 이용하지 않고 행복제 없이 풀어봤습니다...
park1q -- 일만구천도 잘 되는데......;;
by park1q
[2010.10.01 12:13:37]
v상이v 일반구천이 아니고 뭐였나 생각이 잘 안나는데요..
다시 보니..
[일조일] 하면 상이님..처음에 한것은 1000000000002, 두번째것은 10001 나옵니다.
암튼 처음이지만 만나서 다들 반갑습니다.
PS : 행복제가 뭔가요..?
다시 보니..
[일조일] 하면 상이님..처음에 한것은 1000000000002, 두번째것은 10001 나옵니다.
암튼 처음이지만 만나서 다들 반갑습니다.
PS : 행복제가 뭔가요..?
by 마농
[2010.10.01 13:10:22]
v상이v님 답은 오답이었군요... 만단위가 없으면 잘못된 결과가 나오네요.
park1q 님 잘 찾으시네요~
park1q 님 잘 찾으시네요~
by 마농
[2010.10.01 14:52:51]
park1q 님도 오답입니다.
'이십조오십'
'이십조오십'
by v상이v
[2010.10.01 16:22:34]
WITH T AS (
SELECT '이십조오십' AMT FROM DUAL
UNION ALL SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '' FROM DUAL
)
SELECT AMT
,TO_NUMBER(
DECODE(INSTR(V1,'천'),0,'0',SUBSTR(V1,INSTR(V1,'천')-1,1))
||DECODE(INSTR(V1,'백'),0,'0',SUBSTR(V1,INSTR(V1,'백')-1,1))
||DECODE(INSTR(V1,'십'),0,'0',SUBSTR(V1,INSTR(V1,'십')-1,1))
||SUBSTR(V1,LENGTH(V1))
||DECODE(INSTR(V2,'천'),0,'0',SUBSTR(V2,INSTR(V2,'천')-1,1))
||DECODE(INSTR(V2,'백'),0,'0',SUBSTR(V2,INSTR(V2,'백')-1,1))
||DECODE(INSTR(V2,'십'),0,'0',SUBSTR(V2,INSTR(V2,'십')-1,1))
||SUBSTR(V2,LENGTH(V2))
||DECODE(INSTR(V3,'천'),0,'0',SUBSTR(V3,INSTR(V3,'천')-1,1))
||DECODE(INSTR(V3,'백'),0,'0',SUBSTR(V3,INSTR(V3,'백')-1,1))
||DECODE(INSTR(V3,'십'),0,'0',SUBSTR(V3,INSTR(V3,'십')-1,1))
||SUBSTR(V3,LENGTH(V3))
||DECODE(INSTR(V4,'천'),0,'0',SUBSTR(V4,INSTR(V4,'천')-1,1))
||DECODE(INSTR(V4,'백'),0,'0',SUBSTR(V4,INSTR(V4,'백')-1,1))
||DECODE(INSTR(V4,'십'),0,'0',SUBSTR(V4,INSTR(V4,'십')-1,1))
||SUBSTR(V4,LENGTH(V4))
) AS V
FROM (
SELECT AMT
,CASE WHEN MOD(LENGTH(V1),2) = 0 THEN V1||'0' ELSE V1 END V1
,CASE WHEN MOD(LENGTH(V2),2) = 0 THEN V2||'0' ELSE V2 END V2
,CASE WHEN MOD(LENGTH(V3),2) = 0 THEN V3||'0' ELSE V3 END V3
,CASE WHEN MOD(LENGTH(V4),2) = 0 THEN V4||'0' ELSE V4 END V4
FROM (
SELECT AMT
,NVL(SUBSTR(V,0,INSTR(V,'조')-1),0) V1
,NVL(SUBSTR(V,INSTR(V,'조')+1,INSTR(V,'억')-INSTR(V,'조')-1),0) V2
,NVL(SUBSTR(V,INSTR(V,'억')+1,INSTR(V,'만')-INSTR(V,'억')-1),0) V3
,CASE WHEN INSTR(V,'만') > 0 THEN SUBSTR(V,INSTR(AMT,'만')+1)
WHEN INSTR(V,'억') > 0 THEN SUBSTR(V,INSTR(AMT,'억')+1)
WHEN INSTR(V,'조') > 0 THEN SUBSTR(V,INSTR(AMT,'조')+1)
ELSE V
END V4
FROM (SELECT AMT,TRANSLATE(AMT ,'일이삼사오육칠팔구','123456789') AS V FROM T)
)
)
두번째 올린 쿼리만 수정해서 올립니다...
주어진 결과에서만 답을 내려고 짱구를 돌렸네요...ㅡㅅㅜ
오답을 찾아주신것도 감사합니다...
SELECT '이십조오십' AMT FROM DUAL
UNION ALL SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' AMT FROM DUAL
UNION ALL SELECT '' FROM DUAL
)
SELECT AMT
,TO_NUMBER(
DECODE(INSTR(V1,'천'),0,'0',SUBSTR(V1,INSTR(V1,'천')-1,1))
||DECODE(INSTR(V1,'백'),0,'0',SUBSTR(V1,INSTR(V1,'백')-1,1))
||DECODE(INSTR(V1,'십'),0,'0',SUBSTR(V1,INSTR(V1,'십')-1,1))
||SUBSTR(V1,LENGTH(V1))
||DECODE(INSTR(V2,'천'),0,'0',SUBSTR(V2,INSTR(V2,'천')-1,1))
||DECODE(INSTR(V2,'백'),0,'0',SUBSTR(V2,INSTR(V2,'백')-1,1))
||DECODE(INSTR(V2,'십'),0,'0',SUBSTR(V2,INSTR(V2,'십')-1,1))
||SUBSTR(V2,LENGTH(V2))
||DECODE(INSTR(V3,'천'),0,'0',SUBSTR(V3,INSTR(V3,'천')-1,1))
||DECODE(INSTR(V3,'백'),0,'0',SUBSTR(V3,INSTR(V3,'백')-1,1))
||DECODE(INSTR(V3,'십'),0,'0',SUBSTR(V3,INSTR(V3,'십')-1,1))
||SUBSTR(V3,LENGTH(V3))
||DECODE(INSTR(V4,'천'),0,'0',SUBSTR(V4,INSTR(V4,'천')-1,1))
||DECODE(INSTR(V4,'백'),0,'0',SUBSTR(V4,INSTR(V4,'백')-1,1))
||DECODE(INSTR(V4,'십'),0,'0',SUBSTR(V4,INSTR(V4,'십')-1,1))
||SUBSTR(V4,LENGTH(V4))
) AS V
FROM (
SELECT AMT
,CASE WHEN MOD(LENGTH(V1),2) = 0 THEN V1||'0' ELSE V1 END V1
,CASE WHEN MOD(LENGTH(V2),2) = 0 THEN V2||'0' ELSE V2 END V2
,CASE WHEN MOD(LENGTH(V3),2) = 0 THEN V3||'0' ELSE V3 END V3
,CASE WHEN MOD(LENGTH(V4),2) = 0 THEN V4||'0' ELSE V4 END V4
FROM (
SELECT AMT
,NVL(SUBSTR(V,0,INSTR(V,'조')-1),0) V1
,NVL(SUBSTR(V,INSTR(V,'조')+1,INSTR(V,'억')-INSTR(V,'조')-1),0) V2
,NVL(SUBSTR(V,INSTR(V,'억')+1,INSTR(V,'만')-INSTR(V,'억')-1),0) V3
,CASE WHEN INSTR(V,'만') > 0 THEN SUBSTR(V,INSTR(AMT,'만')+1)
WHEN INSTR(V,'억') > 0 THEN SUBSTR(V,INSTR(AMT,'억')+1)
WHEN INSTR(V,'조') > 0 THEN SUBSTR(V,INSTR(AMT,'조')+1)
ELSE V
END V4
FROM (SELECT AMT,TRANSLATE(AMT ,'일이삼사오육칠팔구','123456789') AS V FROM T)
)
)
두번째 올린 쿼리만 수정해서 올립니다...
주어진 결과에서만 답을 내려고 짱구를 돌렸네요...ㅡㅅㅜ
오답을 찾아주신것도 감사합니다...
by park1q
[2010.10.01 17:02:28]
ㅠㅠ. 조단위에서 계속 20으로 나오네요..
집에 가야 되서리..담주에 계속..
근데..영문을 아라비아로 하는것은 찾아 보니..asktom 에서도 10년째 이슈던데..대강 하긴 했는데..화면을 닫아 버려서..ㅠㅠ
즐건 주말 되세요.
집에 가야 되서리..담주에 계속..
근데..영문을 아라비아로 하는것은 찾아 보니..asktom 에서도 10년째 이슈던데..대강 하긴 했는데..화면을 닫아 버려서..ㅠㅠ
즐건 주말 되세요.
by 마농
[2010.10.04 14:23:34]
[행복제가 뭔가요..?] 라는 댓글이 있었네요... 미쳐 못봤네요.
원래 하나의 행이었던 자료를 (select level lvl from dual connect by level <= 4)과 조인하여 4개행으로 복제하여 분리한것을 말합니다.
그것을 다시 Group By 해서 한행으로 다시 합쳤죠.
원래 하나의 행이었던 자료를 (select level lvl from dual connect by level <= 4)과 조인하여 4개행으로 복제하여 분리한것을 말합니다.
그것을 다시 Group By 해서 한행으로 다시 합쳤죠.
by park1q
[2010.10.04 22:30:40]
제가 풀었던건 확실히 오답이네요..ㅠㅠ
고칠려니..으~~substr 에서 머리에 쥐나네요..
답 한번 보구 해봤더니..정답보단 조금 딸리지만
이렇게 ...월추~~
with t AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' amt FROM dual
UNION ALL SELECT '이만구천' FROM dual
UNION ALL SELECT '삼만구천팔백사십' FROM dual
UNION ALL SELECT '이조일' FROM dual
)
SELECT amt,
to_number(
nvl(REGEXP_REPLACE(v1,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+천)?(.+백)?(.+십)?'),'0'))
FROM (SELECT amt,
REGEXP_REPLACE(amt, '(.+)조|.','\1' ) v1,
REGEXP_REPLACE(amt, '(.+조)?(.+)억|.','\2' ) v2,
REGEXP_REPLACE(amt, '(.+(조|억))?(.+)만|.', '\3' ) v3,
REGEXP_REPLACE(amt, '(.+조)?(.+억)?(.+만)?' ) v4
FROM (SELECT TRANSLATE (AMT,'일이삼사오육칠팔구','123456789') amt FROM t )
)
고칠려니..으~~substr 에서 머리에 쥐나네요..
답 한번 보구 해봤더니..정답보단 조금 딸리지만
이렇게 ...월추~~
with t AS
(
SELECT '일백이십조사천오백육십칠억팔천구백일만이천삼백사십오' amt FROM dual
UNION ALL SELECT '이만구천' FROM dual
UNION ALL SELECT '삼만구천팔백사십' FROM dual
UNION ALL SELECT '이조일' FROM dual
)
SELECT amt,
to_number(
nvl(REGEXP_REPLACE(v1,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v1,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v2,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v3,'(.+천)?(.+백)?(.+십)?'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+)천|.','\1'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+천)?(.+)백|.','\2'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+(천|백))?(.+)십|.','\3'),'0')||
nvl(REGEXP_REPLACE(v4,'(.+천)?(.+백)?(.+십)?'),'0'))
FROM (SELECT amt,
REGEXP_REPLACE(amt, '(.+)조|.','\1' ) v1,
REGEXP_REPLACE(amt, '(.+조)?(.+)억|.','\2' ) v2,
REGEXP_REPLACE(amt, '(.+(조|억))?(.+)만|.', '\3' ) v3,
REGEXP_REPLACE(amt, '(.+조)?(.+억)?(.+만)?' ) v4
FROM (SELECT TRANSLATE (AMT,'일이삼사오육칠팔구','123456789') amt FROM t )
)
) 버튼을 클릭하여 작성 하시면 됩니다.