Hello Experts,
I have this below code that works fine. I just got it from AskTom. I was wondering if the same is possible in pure SQL. I would be glad to know if someone can show how it would be. I basically want it to work for floating point numbers.
For Eg:
Run 1:
i/p : '1.1'
o/p: one point one
Run 2:
i/p : '0.1'
o/p: zero point one
Run 3:
i/p : '0.001'
o/p: zero point zero zero one
Run 4:
i/p : '0.0010' --Added zero to the right of 1
o/p: zero point zero zero one
Run 5:
i/p : '00.0010' --Added zero to the left of zero before the point
o/p: zero point zero zero one
create or replace function spell_number(p_number in varchar2) return varchar2 as
type string_array is table of varchar2(255);
v_string string_array := string_array('',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ', ' tridecillion ',
' quaddecillion ', ' quindecillion ',
' sexdecillion ', ' septdecillion ',
' octdecillion ', ' nondecillion ',
' dedecillion ');
v_number varchar2(255);
v_return varchar2(4000);
begin
if instr(p_number, '.') = 0 then
v_number := p_number;
else
v_number := substr(p_number, 1, instr(p_number, '.')-1);
end if;
--
if v_number = '0' or v_number is null then
v_return := 'zero';
else
for i in 1 .. v_string.count
loop
exit when v_number is null;
--
if (substr(v_number, length(v_number)-2, 3) <> 0) then
v_return := to_char(to_date(substr(v_number, length(v_number)-2, 3), 'j'), 'jsp') ||
v_string(i) || v_return;
end if;
v_number := substr(v_number, 1, length(v_number)-3);
end loop;
end if;
--
-- to include decimal places.
--
if p_number like '%.%' then
v_number := substr(p_number, instr(p_number, '.')+1);
v_return := v_return ||' point';
for i in 1 .. length(v_number)
loop
exit when v_number is null;
if substr(v_number, 1, 1) = '0' then
v_return := v_return ||' zero';
else
v_return := v_return ||' '|| to_char(to_date(substr(v_number, 1, 1), 'j'), 'jsp');
end if;
v_number := substr(v_number, 2);
end loop;
end if; -- include decimal places.
return v_return;
end spell_number;
Oracle Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Regards,
Ranagal