Hi,
i was trying to achieve the conversion of a numeric value to literal using sql. It's not an assignement, was just spending my time. Anyway, following i will post the code that i ended with, but it seems to me quite .....long and not that elegant.
Just posting here to ask if u have done something similar or to suggest any changes / corrections on the code.
Thx
(ps: code handles number as big as trillions)
<pre>
select --987.563.000.123,98
--sign of number
decode(sign(theNumber),-1,sNegative) ||
--decode trillions
decode(trunc(Trillion/100),1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || decode(trunc(Trillion/100),0,'', ' ' || s100 || ' ') ||
decode(length(Trillion),1,decode(Trillion,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9),
case trunc(to_number(substr(Trillion,-2))/10)
when 1 then decode(mod(to_number(substr(Trillion,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Trillion,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Trillion,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end
) || decode(Trillion,0,'',sTr) ||
--decode billions
decode(trunc(Billion/100),1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || decode(trunc(Billion/100),0,'', ' ' || s100 || ' ') ||
decode(length(Billion),1,decode(Billion,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9),
case trunc(to_number(substr(Trillion,-2))/10)
when 1 then decode(mod(to_number(substr(Billion,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Billion,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Billion,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end
) || decode(Billion,0,'',sB) ||
--decode millions
decode(trunc(Million/100),1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || decode(trunc(Million/100),0,'', ' ' || s100 || ' ') ||
decode(length(Million),1,decode(Million,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9),
case trunc(to_number(substr(Million,-2))/10)
when 1 then decode(mod(to_number(substr(Million,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Million,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Million,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end
) || decode(Million,0,'',sM) ||
--decode thousand
decode(trunc(Thousand/100),1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || decode(trunc(Thousand/100),0,'', ' ' || s100 || ' ') ||
decode(length(Thousand),1,decode(Thousand,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9),
case trunc(to_number(substr(Thousand,-2))/10)
when 1 then decode(mod(to_number(substr(Thousand,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Thousand,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Thousand,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end
) || decode(Thousand,0,'',sT) ||
--decode simple
decode(trunc(Simple/100),1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || decode(trunc(Simple/100),0,'', ' ' || s100 || ' ') ||
decode(length(Simple),1,decode(Simple,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9),
case trunc(to_number(substr(Simple,-2))/10)
when 1 then decode(mod(to_number(substr(Simple,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Simple,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Simple,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end
) || sCur || sAnd ||
--decode decimals
decode(length(Decimals),1,decode(Decimals,0, s0,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9) || sCent ,
case trunc(to_number(substr(Decimals,-2))/10)
when 1 then decode(mod(to_number(substr(Decimals,-2)),10), 0,s10,1,s11,2,s12,3,s13,4,s14,5,s15,6,s16,7,s17,8,s18,9,s19)
else decode(trunc(to_number(substr(Decimals,-2))/10), 2,s20,3,s30,4,s40,5,s50,6,s60,7,s70,8,s80,9,s90) || ' ' ||
decode(mod(to_number(substr(Decimals,-2)),10) ,1,s1,2,s2,3,s3,4,s4,5,s5,6,s6,7,s7,8,s8,9,s9)
end || sCent
) result
from
(
with splitNum as
(select to_number(:num) N,
trunc(abs(to_number(:num))/1000000000000000,3) Tr, -- 1.000.000.000.000.000
trunc(abs(to_number(:num))/1000000000000,3) B, -- 1.000.000.000.000
trunc(abs(to_number(:num))/1000000000,3) M, -- 1.000.000.000
trunc(abs(to_number(:num))/1000000,3) T, -- 1.000.000
trunc(abs(to_number(:num))/1000,3) S, -- 1.000
abs(to_number(:num)) - trunc(abs(to_number(:num))) D
from dual
) select to_number(decode(instr(Tr,','),0,'000',rpad(substr(Tr,instr(Tr,',')+1),3,0))) Trillion,
to_number(decode(instr(B,','),0,'000',rpad(substr(B,instr(B,',')+1),3,0))) Billion,
to_number(decode(instr(M,','),0,'000',rpad(substr(M,instr(M,',')+1),3,0))) Million,
to_number(decode(instr(T,','),0,'000',rpad(substr(T,instr(T,',')+1),3,0))) Thousand,
to_number(decode(instr(S,','),0,'000',rpad(substr(S,instr(S,',')+1),3,0))) Simple,
to_number(decode(instr(D,','),0,'000',rpad(substr(D,instr(D,',')+1),2,0))) Decimals,
N theNumber
--, abs(N) absNumber
--, N, Tr ,B, M, T,S
from splitNum
),
(
with literals as
(select
'zero' s0, 'one' s1, 'two' s2, 'three' s3, 'four' s4, 'five' s5, 'six' s6, 'seven' s7, 'eight' s8, 'nine' s9,
'ten' s10, 'eleven' s11, 'twelve' s12, 'thirteen' s13, 'fourteen' s14, 'fifteen' s15, 'sixteen' s16, 'seventeen' s17,
'eighteen' s18, 'nineteen' s19,
'twenty' s20, 'thirty' s30, 'fourty' s40, 'fifty' s50, 'sixty' s60, 'seventy' s70, 'eighty' s80, 'ninety' s90,
'hundred' s100,
--following two lines are for languages like greek were 200,300,400,..,900 have their own literals. Implementation not show here
-- 'two hundred' s200, 'three hundred' s300, 'four hundred' s400, 'five hundred' s500, 'six hundred' s600,
-- 'seven hundred' s700, 'eight hundred' s800, 'nine hundred' s900,
' thousand ' sT, ' million ' sM, ' billion ' sB, ' trillion ' sTr ,
' and ' sAND, ' euros ' sCur, ' cents ' sCent, ' minus ' sNegative
from dual)
select * from literals
)
</pre>