A PL/SQL program to convert numbers into words

Dear Bhupinder,

thanks for the code :)

Appreciate your time and effort involved in developing this piece of code.

Best of luck in your pursuit

Abdulrahman

Thanks Bhupinder,
Good piece of work. Keep it up.

Gemsuis

Thanks for the code.
This was very useful.
But this code doesn't work for 100,200...series
You missed to handle an exception.

Regards,
lakshmi

This is very good pl/sql block.

We are using following way for to convert the number into word.

SELECT 1234567 "NUMBER",'$. ' || (TO_CHAR(TO_DATE(1234567,'J'), 'JSP')) "NUMBER IN WORDS" FROM DUAL

Output:
NUMBER NUMBER IN WORDS
1234567 $. ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN

Dear Bhupinder,

Good piece of code. it doesn't work if be execute for Hundreds. ie., 100,200,...900 it returns blank

so, small change is done to make it work

CREATE OR REPLACE FUNCTION f_words (p_amount IN Number) RETURN Varchar2 IS
/*****************************************************************************
--Author          :  Bhupinder Singh
--Creation Date   :  05/03/2007
--Purpose         :  This Function returns amount in words.
--Parameters      :
--1) p_amount     :  Only positive and negative values are allowed.
                     Precision can be entered upto 10 digits and only 2 scales 
                     are allowed e.g 9999999999.99
-------------------------    MODIFICATION HISTORY ----------------------------
WHO              WHEN(Date)    WHY
Bhupinder Singh  18/01/2007    Created.
-------"-------  05/03/2007    Added CASE statement for positive and negative 
                               numbers. 
******************************************************************************/
   -------------------------------------
   -- Index by Tables to store word list
   -------------------------------------
   TYPE typ_word_list IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER;
   t_typ_word_list typ_word_list; 
   TYPE typ_word_gap IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER;
   t_typ_word_gap typ_word_gap; 
   ------------------
   -- Local Variables
   ------------------
   v_amount        Number := p_amount;
   v_amount_length Number;
   v_words         Varchar2(10000);
   v_point_found   Varchar2(1) := 'N';
   v_point_value   Number;
BEGIN
   /*Getting value after point if found */
   v_point_value := SUBSTR(v_amount,(INSTR(v_amount,'.',1) + 1),2);

   /*Checking whether amount has any scale value also */
   v_point_found := CASE WHEN (INSTR(v_amount,'.',1)) = 0 THEN 'N'
                         WHEN (INSTR(v_amount,'.',1)) > 0 THEN 'Y'
                    END;
   /*Converting amount into pure numeric format */
   v_amount := FLOOR(ABS(v_amount));   

   --
   v_amount_length := LENGTH(v_amount);
   --
   t_typ_word_gap(2)  := 'and Paise';
   t_typ_word_gap(3)  := 'Hundred'; 
   t_typ_word_gap(4)  := 'Thousand'; 
   t_typ_word_gap(6)  := 'Lakh'; 
   t_typ_word_gap(8)  := 'Crore';
   t_typ_word_gap(10) := 'Arab';
   --
   FOR i IN 1..99
   LOOP
       t_typ_word_list(i)   :=  To_Char(To_Date(i,'J'),'Jsp');
   END LOOP;
   --
   IF v_amount_length <= 2 
   THEN
       /* Conversion 1 to 99 digits */
       v_words := t_typ_word_list(v_amount);
   ELSIF v_amount_length = 3
   THEN
       /* Conversion for 3 digits till 999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(3);
       IF SUBSTR(v_amount,2,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2));
       END IF;
   ELSIF v_amount_length = 4
   THEN
       /* Conversion for 4 digits till 9999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(4); 
       IF SUBSTR(v_amount,2,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,3,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2));
       END IF;
   ELSIF v_amount_length = 5
   THEN
       /* Conversion for 5 digits till 99999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(4); 
       IF SUBSTR(v_amount,3,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2));
       END IF;   

   ELSIF v_amount_length = 6
   THEN
       /* Conversion for 6 digits till 999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(6); 
       IF SUBSTR(v_amount,2,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,4,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,1))||' '||t_typ_word_gap(3);
       END IF;   
       IF SUBSTR(v_amount,5,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2));
       END IF;
   ELSIF v_amount_length = 7
   THEN
       /* Conversion for 7 digits till 9999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(6); 
       IF SUBSTR(v_amount,3,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,5,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,1))||' '||t_typ_word_gap(3);
       END IF;   
       IF SUBSTR(v_amount,6,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2));
       END IF;
   ELSIF v_amount_length = 8
   THEN
       /* Conversion for 8 digits till 99999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(8); 
       IF SUBSTR(v_amount,2,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(6);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(4);
       END IF;   
       IF SUBSTR(v_amount,6,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,7,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,2));
       END IF;
   ELSIF v_amount_length = 9
   THEN
       /* Conversion for 9 digits till 999999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(8); 
       IF SUBSTR(v_amount,3,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(6);
       END IF;
       IF SUBSTR(v_amount,5,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2))||' '||t_typ_word_gap(4);
       END IF;   
       IF SUBSTR(v_amount,7,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,8,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,2));
       END IF;
   ELSIF v_amount_length = 10
   THEN
       /* Conversion for 10 digits till 9999999999 */
       v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(10); 
       IF SUBSTR(v_amount,2,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(8);
       END IF;
       IF SUBSTR(v_amount,4,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(6);
       END IF;   
       IF SUBSTR(v_amount,6,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2))||' '||t_typ_word_gap(4);
       END IF;
       IF SUBSTR(v_amount,8,1) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,1))||' '||t_typ_word_gap(3);
       END IF;
       IF SUBSTR(v_amount,9,2) != 0
       THEN  
           v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,9,2));
       END IF;
   END IF;
   --
   IF v_point_found = 'Y'
   THEN
       IF v_point_value != 0
       THEN
           v_words := v_words||' '||t_typ_word_gap(2)||' '||t_typ_word_list(CASE WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 1 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)||'0'
                                                                                 WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 2 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)
                                                                            END);
       END IF;
   END IF;
   --
   IF p_amount < 0
   THEN
       v_words := 'Minus '||v_words;
   ELSIF p_amount = 0
   THEN
       v_words := 'Zero';
   END IF;
   IF LENGTH(v_amount) > 10
   THEN
       v_words := 'Value larger than specified precision allowed to convert into words. Maximum 10 digits allowed for precision.';
   END IF;
   RETURN (v_words);

END f_words;
/
sho err

select f_words(1548555) from dual
/

You are right bhavin_rudani. We are also using the same style to spell the numbers. I did not find the need to have this much bigger code...If any one knows please clarify on this.

Thanks,
Vimal

select to_char(to_date('&&yournumber','J'),'JSP') from dual;

Boss, there is a limitation even to this one line code. Its range is only between 1 and 5373484 (Julian date's limitation).

Is there a way to overcome it?

Also, this can help:

select
TO_CHAR(TO_DATE(substr(1234567.123,1,instr(1234567.123,'.')-1),'J'),'JSP')||' and paise '||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(1234567.123,instr(1234567.123,'.')+1,length(1234567.123)),'J'),'JSP'),'MILLION',''),'HUNDRED',''),'THOUSAND',''),'-',' ') as number_char
from dual.

but need more... :)

Dear Bhupender,

Great logic and really appreciable.

Keep it Up....!!!

Regards
Saurabh Mittal

Set Serveroutput On 1000000
Declare
  V_Input NUMBER := &TESTED;
  Function Numbertowords(P_Number In Out Number) Return Varchar2 Is
    V_Words Varchar2(32767) := ' ';
    V_Temp Number;    
    Type Unitsmap Is Table Of Varchar2(250) Index By Binary_Integer;
    Type Tensmap Is Table Of Varchar2(250) Index By Binary_Integer;    
    V_Unitsmap Unitsmap ;
    V_Tensmap Tensmap   ;
  Begin
    V_Unitsmap(0) := 'Zero';
    V_Unitsmap(1) := 'One';
    V_Unitsmap(2) := 'Two';
    V_Unitsmap(3) := 'Three';
    V_Unitsmap(4) := 'Four';
    V_Unitsmap(5) := 'Five';
    V_Unitsmap(6) := 'Six';
    V_Unitsmap(7) := 'Seven';
    V_Unitsmap(8) := 'Eight';
    V_Unitsmap(9) := 'Nine';
    V_Unitsmap(10) := 'Ten';
    V_Unitsmap(11) := 'Eleven';
    V_Unitsmap(12) := 'Twelve';
    V_Unitsmap(13) := 'Thirteen';
    V_Unitsmap(14) := 'Fourteen';
    V_Unitsmap(15) := 'Fifteen';
    V_Unitsmap(16) := 'Sixteen';
    V_Unitsmap(17) := 'Seventeen';
    V_Unitsmap(18) := 'Eighteen';
    V_Unitsmap(19) := 'Nineteen';
    V_Tensmap(2)  := 'Twenty';
    V_Tensmap(3)  := 'Thirty';
    V_Tensmap(4)  := 'Forty';
    V_Tensmap(5)  := 'Fifty';
    V_Tensmap(6)  := 'Sixty';
    V_Tensmap(7)  := 'Seventy';
    V_Tensmap(8)  := 'Eighty';
    V_Tensmap(9)  := 'Ninety';
    If (P_Number = 0) Then
      Return 'Zero';
    End If;
    If (P_Number < 0) Then
      V_Temp := Abs(P_Number);      
      Return 'Minus ' || Numbertowords(V_Temp);
    End If;
    V_Temp := TRUNC(P_Number / 1000000);
    If ( V_Temp > 0) Then 
      V_Words := V_WORDS || Numbertowords(V_Temp) || ' Million';
      P_Number := Mod(P_Number,1000000);
    End If;
    V_Temp := TRUNC(P_Number / 1000);
    If ( V_Temp > 0)Then
      V_Words := V_Words || Numbertowords(V_Temp) || ' Thousand';
      P_Number := Mod(P_Number,1000);
    End If;
    V_Temp := Trunc(P_Number / 100);
    If ( V_Temp > 0) Then
      V_Words := V_Words ||  Numbertowords(V_Temp) || ' Hundred ';
      P_Number := Mod(P_Number,100);
    End If;
    V_Temp := P_Number;
    If (V_Temp > 0) Then
      If (V_Words != ' ') Then
        V_Words := V_Words || 'And ';
       End If;
      If (V_Temp < 20) Then
        V_Words := V_Words || V_Unitsmap(V_Temp);
        Return V_Words;
      Else
       V_Temp := TRUNC(P_Number/ 10);
        V_Words := V_Words || V_Tensmap(V_Temp);
        If ((Mod(P_Number ,10)) > 0) Then
          V_Words :=V_Words|| '-' ||V_Unitsmap(Mod(P_Number,10));
        End If;
      End If;        
    End If;
    Return V_Words;
  End;
Begin    
  Dbms_Output.Put_Line(&TESTED||' ='||Numbertowords(V_Input));
End;

All the above code is good, but I've reduced the coding to the following and it works great:

 create or replace function spell_number_inr( p_number in varchar2 )
    return varchar2
    -- original by Tom Kyte
    -- modified to include decimal places
    as
        type myArray is table of varchar2(255);
        l_str    myArray := myArray( '',
                               ' thousand ', ' lakhs ', 'million',
                               ' billion ', ' trillion ',
                              ' quadrillion ', ' quintillion ',
                              ' sextillion ', ' septillion ',
                              ' octillion ', ' nonillion ',
                              ' decillion ', ' undecillion ',
                              ' duodecillion ' );
       l_num varchar2(50) default trunc( p_number );
       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;
 
       l_return := l_return || ' Rupees';
 
       -- beginning of section added to include decimal places:
       if p_number like '%.%'
      then
              l_return := l_return || ' and';
           l_num := substr( p_number, instr( p_number, '.' )+1 );
           l_return := l_return
                          || ' '
                          || to_char(
                                 to_date(l_num,'j' ),
                          'jsp' );
 
              l_return := l_return || ' Paisa';
       end if;
       -- end of section added to include decimal places
 
       return l_return;
   end spell_number_inr;
   /