Home » SQL & PL/SQL » SQL & PL/SQL » Converting Numbers into Wordings (merged)
Converting Numbers into Wordings (merged) [message #386497] Sun, 15 February 2009 21:58 Go to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Some people use THOUSAND, LAC and CRORE. Other people use THOUSAND, MILLION, BILLION, TRILLION, etc. Following codes will convert numbers into wordings in the later format:

------------------------------------------------------------------
------------------------------------------------------------------
---function TO_WORD_TRIL to convert numeric values into wording
------------------------------------------------------------------
------------------------------------------------------------------

CREATE OR REPLACE FUNCTION twodigit(innumtwdigit IN VARCHAR2)
  RETURN VARCHAR2
  IS 
    teennum1tw VARCHAR2(125) ;
    teennum2tw VARCHAR2(125) ;   
    teennum3tw VARCHAR2(125) ;   
    teennum4tw VARCHAR2(125) ;   

	TYPE TeensTabTyp IS TABLE OF CHAR(20)
		INDEX BY BINARY_INTEGER ;
	TYPE TensTabTyp IS TABLE OF CHAR(9)
		INDEX BY BINARY_INTEGER ;

	teens TeensTabTyp ;
	tens   TensTabTyp ;

BEGIN
		teens(1) := 'one ' 	;
		teens(2) := 'two ' 	;
		teens(3) := 'three ' 	;
		teens(4) := 'four ' 	;
		teens(5) := 'five ' 	;
		teens(6) := 'six ' 	;
		teens(7) := 'seven ' 	;
		teens(8) := 'eight ' 	;
		teens(9) := 'nine ' 	;
		teens(10):= 'ten ' 	;
		teens(11):= 'eleven ' 	;
		teens(12):= 'tweleve ' 	;
		teens(13):= 'thirteen '	;
		teens(14):= 'fourteen '	;
		teens(15):= 'fifteen '	;
		teens(16):= 'sixteen '	;
		teens(17):= 'seventeen ';
		teens(18):= 'eighteen '	;
		teens(19):= 'nineteen '	;

		tens(1) := 'twenty '	;
		tens(2) := 'thirty '	;
		tens(3) := 'forty '	;
		tens(4) := 'fifty '	;
		tens(5) := 'sixty '	;
		tens(6) := 'seventy '	;
		tens(7) := 'eighty '	;
		tens(8) := 'ninety '	;

    teennum1tw :='' ;
--    teennum2tw :=' ZERO ' ;   
    teennum2tw :='' ;   
    teennum3tw :='' ;   
    teennum4tw :='' ;   
      
   IF TO_NUMBER(SUBSTR(innumtwdigit,1,2))>0 THEN 
	   IF TO_NUMBER(SUBSTR(innumtwdigit,1,2))<20 THEN 
	   	teennum2tw	:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtwdigit,1,2))))) ;
	   END IF ;

	   IF SUBSTR(innumtwdigit,1,2)>=20 THEN 
		teennum2tw	:=LTRIM(RTRIM(tens(TRUNC(TO_NUMBER(SUBSTR(innumtwdigit,1,2))/10,0)-1 ))) ;
		IF TO_NUMBER(SUBSTR(innumtwdigit,2,1))>0 THEN 
			teennum3tw	:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtwdigit,2,1))))) ;
		END IF ;
	   END IF ;
   END IF ;

   teennum4tw:=  teennum2tw||' '||teennum3tw ; 
   
   RETURN(teennum4tw) ;

   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'No data found') ; 
END twodigit ;
. 
/



CREATE OR REPLACE FUNCTION trdigit(innumtrdigit IN VARCHAR2)
  RETURN VARCHAR2
  IS 
    teennum1tr VARCHAR2(125) ;
    teennum2tr VARCHAR2(125) ;   
    teennum3tr VARCHAR2(125) ;   
    teennum4tr VARCHAR2(125) ;   
    
	TYPE TeensTabTyp IS TABLE OF CHAR(20)
		INDEX BY BINARY_INTEGER ;
	TYPE TensTabTyp IS TABLE OF CHAR(9)
		INDEX BY BINARY_INTEGER ;

	teens TeensTabTyp ;
	tens   TensTabTyp ;

BEGIN
		teens(1) := 'one '	;
		teens(2) := 'two '	;
		teens(3) := 'three '	;
		teens(4) := 'four '	;
		teens(5) := 'five '	;
		teens(6) := 'six '	;
		teens(7) := 'seven '	;
		teens(8) := 'eight '	;
		teens(9) := 'nine '	;
		teens(10):= 'ten '	;
		teens(11):= 'eleven '	;
		teens(12):= 'tweleve '	;
		teens(13):= 'thirteen '	;
		teens(14):= 'fourteen '	;
		teens(15):= 'fifteen '	;
		teens(16):= 'sixteen '	;
		teens(17):= 'seventeen ';
		teens(18):= 'eighteen ' ;
		teens(19):= 'nineteen ' ;

		tens(1) := 'twenty '	;
		tens(2) := 'thirty '	;
		tens(3) := 'forty '	;
		tens(4) := 'fifty '	;
		tens(5) := 'sixty '	;
		tens(6) := 'seventy '	;
		tens(7) := 'eighty '	;
		tens(8) := 'ninety '	;

    teennum1tr :='' ;
    teennum2tr :='' ;   
--    teennum2tr :=' ZERO ' ;   
    teennum3tr :='' ;   
    teennum4tr :='' ;   

   IF TO_NUMBER(SUBSTR(innumtrdigit,1,1))>0 THEN  
   	teennum1tr:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtrdigit,1,1)) ))) ||' HUNDRED ';
   END IF ;

   IF TO_NUMBER(SUBSTR(innumtrdigit,2,2))>0 THEN 
   	teennum2tr	:=twodigit(SUBSTR(innumtrdigit,2,2)) ;
   END IF ;

   teennum4tr:=    teennum1tr||teennum2tr||' '||teennum3tr ; 
   
   RETURN(teennum4tr) ;

   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'No data found') ; 
END trdigit ;
.
/



CREATE OR REPLACE FUNCTION TO_WORD_TRIL(innumfull IN NUMBER) 
  RETURN VARCHAR2
   IS
     wordconvtk 	VARCHAR2(250) ;
     wordconvps 	VARCHAR2(100) ;
     wordconvwd 	VARCHAR2(350) ;

     left3    	VARCHAR2(3);
     subs4_3  	VARCHAR2(3);
     subs7_3  	VARCHAR2(3);
     subs10_3  	VARCHAR2(3);
     subs13_3  	VARCHAR2(3);
     subs16_3  	VARCHAR2(3);
     right2   	VARCHAR2(2);
     
     gotnumberfull VARCHAR2(50);
BEGIN

    --999,999,999,999,999,999.99 

    gotnumberfull :=LTRIM(RTRIM(TO_CHAR(ROUND(ABS(innumfull),2))));

    IF INSTR(gotnumberfull,'.')<1 THEN
    	gotnumberfull:=gotnumberfull||'.00';
    END IF;
    IF INSTR(gotnumberfull,'.')>0 THEN
	    IF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))=0 THEN
        	gotnumberfull:=gotnumberfull||'00';
	    ELSIF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))=1 THEN
        	gotnumberfull:=gotnumberfull||'0';
	    ELSIF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))>2 THEN
        	gotnumberfull:=SUBSTR(gotnumberfull,1,INSTR(gotnumberfull,'.')-1)||
        		SUBSTR(gotnumberfull,INSTR(gotnumberfull,'.'),3) ;
	    END IF;
    END IF;

     IF TO_NUMBER(gotnumberfull)>999999999999999999.99 then 
        wordconvwd:='THE NUMBER EXCEEDS Amount: 999,999,999,999,999,999.99';
	RETURN(wordconvwd) ;
     END IF;


     left3    	:=SUBSTR(LPAD(gotnumberfull,21,'0'), 1,3) ; 
     subs4_3  	:=SUBSTR(LPAD(gotnumberfull,21,'0'), 4,3) ; 
     subs7_3  	:=SUBSTR(LPAD(gotnumberfull,21,'0'), 7,3) ; 
     subs10_3  	:=SUBSTR(LPAD(gotnumberfull,21,'0'),10,3) ;  
     subs13_3  	:=SUBSTR(LPAD(gotnumberfull,21,'0'),13,3) ;       
     subs16_3  	:=SUBSTR(LPAD(gotnumberfull,21,'0'),16,3) ;       
     right2   	:=SUBSTR(LPAD(gotnumberfull,21,'0'),20,2) ; 



  wordconvtk:=' ' ;
  IF TO_NUMBER(left3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(left3)))  ||' THOUSAND ' ;
  END IF ;

  IF TO_NUMBER(subs4_3)>0 OR TO_NUMBER(left3)>0 THEN 
    --This is the breakpoint of trillion. So, if subs4_3=0 but left3<>0 TRILLION must be displayed
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs4_3)))||' TRILLION ' ;
  END IF ;
  IF TO_NUMBER(subs7_3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs7_3)))||' BILLION ' ;
  END IF ;    
  IF TO_NUMBER(subs10_3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs10_3)))||' MILLION ' ;
  END IF ;
  IF TO_NUMBER(subs13_3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs13_3)))||' THOUSAND ' ;
  END IF ;
  IF TO_NUMBER(subs16_3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs16_3))) ;
  END IF ;
  IF TO_NUMBER(right2)>0 THEN 
       wordconvps:=twodigit(right2) ;
  ELSE
       wordconvps:=NULL ;
  END IF;

  SELECT DECODE(LTRIM(RTRIM(wordconvtk)),NULL,NULL,INITCAP(wordconvtk)||' DOLLAR ')||
         DECODE(wordconvps,NULL,NULL,INITCAP(wordconvps)||' CENT ')||
	 'ONLY' 
    INTO wordconvwd 
    FROM DUAL; 

  IF innumfull     <0 THEN
	RETURN('MINUS '||wordconvwd) ;
  ELSIF innumfull  =0 THEN
	RETURN('ZERO') ;
  ELSE
	RETURN(wordconvwd) ;
  END IF;		

   
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'There is some error') ; 
   END TO_WORD_TRIL ;   
.
/

GRANT EXECUTE ON TO_WORD_TRIL TO PUBLIC;
COMMIT;



SELECT TO_WORD_TRIL(12345678901234.56) FROM DUAL;

Mohd. Golam Hossain
Uttara, Dhaka, Bangladesh
Cell: +8801715332257
Mail: m_golam_hossain@yahoo.com; it@bfcc-bd.com

[Updated on: Sun, 15 February 2009 23:16] by Moderator

Report message to a moderator

Converting Numbers into Wordings-CRORE [message #386498 is a reply to message #386497] Sun, 15 February 2009 22:01 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Some days back somebody asked me how to convert numbers into wordings. I know every Oracle Programmer does it in his/her own way. I also do it my way as below:
------------------------------------------------------------------
------------------------------------------------------------------
---function TO_WORD_CRORE to convert numeric values into wording
------------------------------------------------------------------
------------------------------------------------------------------

CREATE OR REPLACE FUNCTION twodigit(innumtwdigit IN VARCHAR2)
  RETURN VARCHAR2
  IS 
    teennum1tw VARCHAR2(125) ;
    teennum2tw VARCHAR2(125) ;   
    teennum3tw VARCHAR2(125) ;   
    teennum4tw VARCHAR2(125) ;   

	TYPE TeensTabTyp IS TABLE OF CHAR(20)
		INDEX BY BINARY_INTEGER ;
	TYPE TensTabTyp IS TABLE OF CHAR(9)
		INDEX BY BINARY_INTEGER ;

	teens TeensTabTyp ;
	tens   TensTabTyp ;

BEGIN
		teens(1) := 'one ' 	;
		teens(2) := 'two ' 	;
		teens(3) := 'three ' 	;
		teens(4) := 'four ' 	;
		teens(5) := 'five ' 	;
		teens(6) := 'six ' 	;
		teens(7) := 'seven ' 	;
		teens(8) := 'eight ' 	;
		teens(9) := 'nine ' 	;
		teens(10):= 'ten ' 	;
		teens(11):= 'eleven ' 	;
		teens(12):= 'tweleve ' 	;
		teens(13):= 'thirteen '	;
		teens(14):= 'fourteen '	;
		teens(15):= 'fifteen '	;
		teens(16):= 'sixteen '	;
		teens(17):= 'seventeen ';
		teens(18):= 'eighteen '	;
		teens(19):= 'nineteen '	;

		tens(1) := 'twenty '	;
		tens(2) := 'thirty '	;
		tens(3) := 'forty '	;
		tens(4) := 'fifty '	;
		tens(5) := 'sixty '	;
		tens(6) := 'seventy '	;
		tens(7) := 'eighty '	;
		tens(8) := 'ninety '	;

    teennum1tw :='' ;
--    teennum2tw :=' ZERO ' ;   
    teennum2tw :='' ;   
    teennum3tw :='' ;   
    teennum4tw :='' ;   
      
   IF TO_NUMBER(SUBSTR(innumtwdigit,1,2))>0 THEN 
	   IF TO_NUMBER(SUBSTR(innumtwdigit,1,2))<20 THEN 
	   	teennum2tw	:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtwdigit,1,2))))) ;
	   END IF ;

	   IF SUBSTR(innumtwdigit,1,2)>=20 THEN 
		teennum2tw	:=LTRIM(RTRIM(tens(TRUNC(TO_NUMBER(SUBSTR(innumtwdigit,1,2))/10,0)-1 ))) ;
		IF TO_NUMBER(SUBSTR(innumtwdigit,2,1))>0 THEN 
			teennum3tw	:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtwdigit,2,1))))) ;
		END IF ;
	   END IF ;
   END IF ;

   teennum4tw:=  teennum2tw||' '||teennum3tw ; 
   
   RETURN(teennum4tw) ;

   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'No data found') ; 
END twodigit ;
. 
/



CREATE OR REPLACE FUNCTION trdigit(innumtrdigit IN VARCHAR2)
  RETURN VARCHAR2
  IS 
    teennum1tr VARCHAR2(125) ;
    teennum2tr VARCHAR2(125) ;   
    teennum3tr VARCHAR2(125) ;   
    teennum4tr VARCHAR2(125) ;   
    
	TYPE TeensTabTyp IS TABLE OF CHAR(20)
		INDEX BY BINARY_INTEGER ;
	TYPE TensTabTyp IS TABLE OF CHAR(9)
		INDEX BY BINARY_INTEGER ;

	teens TeensTabTyp ;
	tens   TensTabTyp ;

BEGIN
		teens(1) := 'one ' 	;
		teens(2) := 'two ' 	;
		teens(3) := 'three ' 	;
		teens(4) := 'four ' 	;
		teens(5) := 'five ' 	;
		teens(6) := 'six ' 	;
		teens(7) := 'seven ' 	;
		teens(8) := 'eight ' 	;
		teens(9) := 'nine ' 	;
		teens(10):= 'ten ' 	;
		teens(11):= 'eleven ' 	;
		teens(12):= 'tweleve ' 	;
		teens(13):= 'thirteen '	;
		teens(14):= 'fourteen '	;
		teens(15):= 'fifteen '	;
		teens(16):= 'sixteen '	;
		teens(17):= 'seventeen ';
		teens(18):= 'eighteen '	;
		teens(19):= 'nineteen '	;

		tens(1) := 'twenty '	;
		tens(2) := 'thirty '	;
		tens(3) := 'forty '	;
		tens(4) := 'fifty '	;
		tens(5) := 'sixty '	;
		tens(6) := 'seventy '	;
		tens(7) := 'eighty '	;
		tens(8) := 'ninety '	;

    teennum1tr :='' ;
    teennum2tr :='' ;   
--    teennum2tr :=' ZERO ' ;   
    teennum3tr :='' ;   
    teennum4tr :='' ;   

   IF TO_NUMBER(SUBSTR(innumtrdigit,1,1))>0 THEN  
   	teennum1tr:=LTRIM(RTRIM(teens(TO_NUMBER(SUBSTR(innumtrdigit,1,1)) ))) ||' HUNDRED ';
   END IF ;

   IF TO_NUMBER(SUBSTR(innumtrdigit,2,2))>0 THEN 
   	teennum2tr	:=twodigit(SUBSTR(innumtrdigit,2,2)) ;
   END IF ;

   teennum4tr:=    teennum1tr||teennum2tr||' '||teennum3tr ; 
   
   RETURN(teennum4tr) ;

   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'No data found') ; 
END trdigit ;
.
/



CREATE OR REPLACE FUNCTION TO_WORD_CRORE(innumfull IN NUMBER) 
  RETURN VARCHAR2
   IS
     wordconvtk 	VARCHAR2(150) ;
     wordconvps 	VARCHAR2(125) ;
     wordconvwd 	VARCHAR2(150) ;

     left3    	VARCHAR2(3);
     subs4_3  	VARCHAR2(3);
     subs7_2  	VARCHAR2(2);
     subs9_2  	VARCHAR2(2);
     subs11_3  	VARCHAR2(3);
     right2   	VARCHAR2(2);
     
     gotnumberfull VARCHAR2(50);
BEGIN

    --999,999,99,99,999.99 

    gotnumberfull :=LTRIM(RTRIM(TO_CHAR(ROUND(ABS(innumfull),2))));

    IF INSTR(gotnumberfull,'.')<1 THEN
    	gotnumberfull:=gotnumberfull||'.00';
    END IF;
    IF INSTR(gotnumberfull,'.')>0 THEN
	    IF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))=0 THEN
        	gotnumberfull:=gotnumberfull||'00';
	    ELSIF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))=1 THEN
        	gotnumberfull:=gotnumberfull||'0';
	    ELSIF (LENGTH(gotnumberfull)-INSTR(gotnumberfull,'.'))>2 THEN
        	gotnumberfull:=SUBSTR(gotnumberfull,1,INSTR(gotnumberfull,'.')-1)||
        		SUBSTR(gotnumberfull,INSTR(gotnumberfull,'.'),3) ;
	    END IF;
    END IF;

     IF TO_NUMBER(gotnumberfull)>9999999999999.99 then 
        wordconvwd:='THE NUMBER EXCEEDS Amount: 999,999,99,99,999.99';
	RETURN(wordconvwd) ;
     END IF;


     left3    	:=SUBSTR(LPAD(gotnumberfull,16,'0'),1,3)  ; 
     subs4_3  	:=SUBSTR(LPAD(gotnumberfull,16,'0'),4,3)  ; 
     subs7_2  	:=SUBSTR(LPAD(gotnumberfull,16,'0'),7,2)  ; 
     subs9_2  	:=SUBSTR(LPAD(gotnumberfull,16,'0'),9,2)  ;  
     subs11_3  	:=SUBSTR(LPAD(gotnumberfull,16,'0'),11,3) ;       
     right2   	:=SUBSTR(LPAD(gotnumberfull,16,'0'),15,2) ; 



  wordconvtk:=' ' ;
  IF TO_NUMBER(left3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(left3)))||' THOUSAND ' ;
  END IF ;

  IF TO_NUMBER(subs4_3)>0 OR TO_NUMBER(left3)>0 THEN 
    --This is the breakpoint of crore. So, if subs4_3=0 but left3<>0 CRORE must be displayed
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs4_3)))||' CRORE ' ;
  END IF ;
  IF TO_NUMBER(subs7_2)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(twodigit(subs7_2)))||' LAC ' ;
  END IF ;    
  IF TO_NUMBER(subs9_2)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(twodigit(subs9_2)))||' THOUSAND ' ;
  END IF ;
  IF TO_NUMBER(subs11_3)>0 THEN 
    wordconvtk:=LTRIM(RTRIM(wordconvtk))||' '||LTRIM(RTRIM(trdigit(subs11_3))) ;
  END IF ;
  IF TO_NUMBER(right2)>0 THEN 
       wordconvps:=twodigit(right2) ;
  ELSE
       wordconvps:=NULL ;
  END IF;

  SELECT DECODE(LTRIM(RTRIM(wordconvtk)),NULL,NULL,INITCAP(wordconvtk)||' TAKA ')||
         DECODE(wordconvps,NULL,NULL,INITCAP(wordconvps)||' PAISA ')||
	 'ONLY' 
    INTO wordconvwd 
    FROM DUAL; 

  IF innumfull     <0 THEN
	RETURN('MINUS '||wordconvwd) ;
  ELSIF innumfull  =0 THEN
	RETURN('ZERO') ;
  ELSE
	RETURN(wordconvwd) ;
  END IF;		

   
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN  
          RAISE_APPLICATION_ERROR(-20100,'There is some error') ; 
   END TO_WORD_CRORE ;   
.
/

GRANT EXECUTE ON TO_WORD_CRORE TO PUBLIC;
COMMIT;




Now, we can test the function with the following command:
SELECT TO_WORD_CRORE(123456789.12) FROM dual;


Mohd. Golam Hossain
Uttara, Dhaka, Bangladesh.
Cell: +8801715332257
Mail: m_golam_hossain@yahoo.com; it@bfcc-bd.com

[Updated on: Sun, 15 February 2009 22:22]

Report message to a moderator

Re: Converting Numbers into Wordings (merged) [message #386515 is a reply to message #386497] Sun, 15 February 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forum is likely not the more appropriate place to post this, OraFAQ contains Script and Papers sections in the Wiki.
Have a look at Home page.

Regards
Michel
Re: Converting Numbers into Wordings (merged) [message #386641 is a reply to message #386515] Mon, 16 February 2009 06:11 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps this thread might be interesting.

MHE
Previous Topic: Collection Methods
Next Topic: how to use INDEX?
Goto Forum:
  


Current Time: Sun Dec 04 15:02:47 CST 2016

Total time taken to generate the page: 0.15276 seconds