Convert Number to Word
September 6, 2010
Leave a comment
SQL> select to_char(to_date(5358452,'J'),'JSP') from dual; TO_CHAR(TO_DATE(5358452,'J'),'JSP') ---------------------------------------------------------------------- FIVE MILLION THREE HUNDRED FIFTY-EIGHT THOUSAND FOUR HUNDRED FIFTY-TWO
Restriction: It can conversion from 1 to 5373484 and it can not conversion decimal number.
A function for convert number to word
create or replace function spell_number( p_number in number ) return varchar2 as type myArray is table of varchar2(255); l_str myArray := myArray( '', ' thousand ' , ' million ', ' billion ' , ' trillion ', ' quadrillion ' , ' quintillion ', ' dont read ' , ' 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; return l_return; end; /
Example
SQL> Select spell_number( 1234567899 ) From dual; SPELL_NUMBER(1234567899) ------------------------ One billion Two Hundred Thirty-Four million Five Hundred Sixty-Seven thousand Eight Hundred Ninety-Nine
Categories: Convert Number to Word, How To



