10 Replies Latest reply on Dec 5, 2008 7:29 AM by Karthick2003

# Convert Number into word.....

Hi All,

I am using following query to convery number into word and it is working fine.

SELECT TO_CHAR(TO_DATE(100,'J'),'JSP') FROM DUAL

but if i want to view the values after decimal i am getting error. Please refer below query

SELECT TO_CHAR(TO_DATE(100.50,'J'),'JSP') FROM DUAL

Can anyone help me out.?

Thanks,
• ###### 1. Re: Convert Number into word.....
Is the .50 "and fifty pence" (i.e. currency) or is it "point five zero"? The first one will be easier
• ###### 2. Re: Convert Number into word.....
Hi William
It is point five Zero ,
It is used as currency column , i want to show in word format..

• ###### 3. Re: Convert Number into word.....
``````Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace
2  function spell_number( p_number in number )
3  return varchar2
4  -- original by Tom Kyte
5  -- modified to include decimal places
6  as
7      type myArray is table of varchar2(255);
8      l_str    myArray := myArray( '',
9                             ' thousand ', ' million ',
10                             ' billion ', ' trillion ',
11                             ' quadrillion ', ' quintillion ',
12                             ' sextillion ', ' septillion ',
13                             ' octillion ', ' nonillion ',
14                             ' decillion ', ' undecillion ',
15                             ' duodecillion ' );
16      l_num varchar2(50) default trunc( p_number );
17      l_return varchar2(4000);
18  begin
19      for i in 1 .. l_str.count
20      loop
21          exit when l_num is null;
22
23          if ( substr(l_num, length(l_num)-2, 3) <> 0 )
24          then
25              l_return := to_char(
26                              to_date(
27                               substr(l_num, length(l_num)-2, 3),
28                                 'J' ),
29                          'Jsp' ) || l_str(i) || l_return;
30          end if;
31          l_num := substr( l_num, 1, length(l_num)-3 );
32      end loop;
33
34      -- beginning of section added to include decimal places:
35      if to_char( p_number ) like '%.%'
36      then
37          l_num := substr( p_number, instr( p_number, '.' )+1 );
38          if l_num > 0
39          then
40              l_return := l_return || ' point';
41              for i in 1 .. length (l_num)
42              loop
43                  exit when l_num is null;
44                  if substr( l_num, 1, 1 ) = '0'
45                  then
46                      l_return := l_return || ' zero';
47                  else
48                      l_return := l_return
49                      || ' '
50                      || to_char(
51                             to_date(
52                             substr( l_num, 1, 1),
53                               'j' ),
54                         'jsp' );
55                  end if;
56                  l_num := substr( l_num, 2 );
57              end loop;
58          end if;
59      end if;
60      -- end of section added to include decimal places
61
62      return l_return;
63  end spell_number;
64  /

Function created.

SQL> select spell_number('99.999') from dual
2  /

SPELL_NUMBER('99.999')
--------------------------------------------------------------------------------
Ninety-Nine point nine nine nine

SQL> select spell_number('9979689769796897976979869.967859999') from dual
2  /

SPELL_NUMBER('9979689769796897976979869.967859999')
--------------------------------------------------------------------------------
Nine septillion Nine Hundred Seventy-Nine sextillion Six Hundred Eighty-Nine qui
ntillion Seven Hundred Sixty-Nine quadrillion Seven Hundred Ninety-Six trillion
Eight Hundred Ninety-Seven billion Nine Hundred Seventy-Six million Nine Hundred
Seventy-Nine thousand Eight Hundred Sixty-Nine point nine six seven eight five
nine nine nine nine``````

Thanks,
Karthick.

Message was edited by:
karthick_arp
• ###### 4. Re: Convert Number into word.....
If it is "point five zero" you'll have to separate out each digit after the decimal point and convert each one with a separate TO_CHAR(TO_DATE(...,'J','JSP')) expression. If you can use "and fifty cents" then you can just use something like TRUNC(MOD(the_amount,1) *100) to make the decimal fraction into a whole number from 0 to 99, and convert that number into words.
• ###### 5. Re: Convert Number into word.....
Its all there done before!!! only pain we have to take is to search for it :-) and asktom is a great place to search. I get most of my answers there!!!
• ###### 6. Re: Convert Number into word.....
I always thought "septillion" was a bit over the top ;)
• ###### 7. Re: Convert Number into word.....
Hi Team
i got very nice idea to user decode ..

select decode(trunc(54334.69),0,'ZERO',to_char(to_date(trunc(54334.69),'J'),'JSP'))
|| ' DOLLARS AND ' || decode(trunc(mod(54334.69,1)*100),0,'ZERO',
to_char(to_date(trunc(mod(54334.69,1)*100),'J'),'JSP')) || ' CENTS'
from dual;

Column
----------------------------------------------------------------------------------------------------
FIFTY-FOUR THOUSAND THREE HUNDRED THIRTY-FOUR DOLLARS AND SIXTY-NINE CENTS

Thanks all
• ###### 8. Re: Convert Number into word.....
Nice idea. What if we deal with millions of dollars? Or if you program something for Mr. Lakshmi Mittal, then lets talk about several billions.

• ###### 9. Re: Convert Number into word.....
Hi Karthi can u send me the code to convert an amount of 54356786.75 in Indian Currency format and last it should display 75 paisa

Regards,
RR
• ###### 10. Re: Convert Number into word.....
You mean like this...
``````SQL> create or replace function spell_number_inr( p_number in varchar2 )
2  return varchar2
3  -- original by Tom Kyte
4  -- modified to include decimal places
5  as
6      type myArray is table of varchar2(255);
7      l_str    myArray := myArray( '',
8                             ' thousand ', ' million ',
9                             ' billion ', ' trillion ',
10                             ' quadrillion ', ' quintillion ',
11                             ' sextillion ', ' septillion ',
12                             ' octillion ', ' nonillion ',
13                             ' decillion ', ' undecillion ',
14                             ' duodecillion ' );
15      l_num varchar2(50) default trunc( p_number );
16      l_return varchar2(4000);
17  begin
18      for i in 1 .. l_str.count
19      loop
20          exit when l_num is null;
21
22          if ( substr(l_num, length(l_num)-2, 3)<>0 )
23          then
24              l_return := to_char(
25                              to_date(
26                               substr(l_num, length(l_num)-2, 3),
27                                 'J' ),
28                          'Jsp' ) || l_str(i) || l_return;
29          end if;
30          l_num := substr( l_num, 1, length(l_num)-3 );
31      end loop;
32
33      l_return := l_return || ' Rupees';
34
35      -- beginning of section added to include decimal places:
36      if p_number like '%.%'
37      then
38             l_return := l_return || ' and';
39          l_num := substr( p_number, instr( p_number, '.' )+1 );
40          l_return := l_return
41                         || ' '
42                         || to_char(
43                                to_date(l_num,'j' ),
44                         'jsp' );
45
46             l_return := l_return || ' Paisa';
47      end if;
48      -- end of section added to include decimal places
49
50      return l_return;
51  end spell_number_inr;
52  /

Function created.

SQL> select spell_number_inr(99.99) from dual
2  /

SPELL_NUMBER_INR(99.99)
----------------------------------------------------------------------------------------------------
Ninety-Nine Rupees and ninety-nine Paisa

SQL> select spell_number_inr(100) from dual
2  /

SPELL_NUMBER_INR(100)
----------------------------------------------------------------------------------------------------
One Hundred Rupees

SQL> select spell_number_inr(100.45) from dual
2  /

SPELL_NUMBER_INR(100.45)
----------------------------------------------------------------------------------------------------
One Hundred Rupees and forty-five Paisa

SQL> select spell_number_inr(343300.75) from dual
2  /

SPELL_NUMBER_INR(343300.75)
----------------------------------------------------------------------------------------------------
Three Hundred Forty-Three thousand Three Hundred Rupees and seventy-five Paisa``````
And one thing don't re invoke old posts.

Thanks,
Karthick.