Convert Numbers into Words using PL/SQL

Introduction

This function will provide the facility to convert a number to words. This function can accept value one to duodecillion. It also accept decimal value, if the decimal value is more than 2 digit then it will use upto 2 decimal only.

This is a improved version of Tom Kyte solution.

Create below procedure:

CREATE OR REPLACE function spell_number( p_number in number)
return varchar2
as
  type myArray is table of varchar2(20);
  l_str    myArray := myArray( '',
                         ' thousand ', ' million ',
                         ' billion ', ' trillion ',
                         ' quadrillion ', ' quintillion ',
                         ' sextillion ', ' septillion ',
                         ' octillion ', ' nonillion ',
                         ' decillion ', ' undecillion ',
                         ' duodecillion ' );

  l_num varchar2(50) default trunc( p_number );
  l_dec varchar2(50);
  l_return varchar2(4000);
begin
  for i in 1 .. l_str.count loop
    exit when l_num is null;
    if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then
      l_return := to_char(
                    to_date(
                      substr(l_num, length(l_num)-2, 3),
                            'J' ),
                     'Jsp' ) || l_str(i) || l_return;
    end if;
    l_num := substr( l_num, 1, length(l_num)-3 );
  end loop;
  if instr(p_number, '.') > 0 then
    --l_dec := substr(p_number, instr(p_number, '.')+1);
    l_dec := substr(ltrim(to_char(p_number-trunc(p_number), '.99')), 2);
    --dbms_output.put_line(l_dec);
    l_return := ltrim(rtrim(l_return)) || ' and ' || ltrim(rtrim(spell_number(l_dec)));
  end if;
  return replace(l_return, '-', ' ');
end;
/

Usage

SQL> select spell_number(1342344322332) from dual;

SPELL_NUMBER(1342344322332)
--------------------------------------------------------------------------------
One trillion Three Hundred Forty Two billion Three Hundred Forty Four million Th
ree Hundred Twenty Two thousand Three Hundred Thirty Two