Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, nisha.

Asked: February 07, 2001 - 4:00 pm UTC

Last updated: April 22, 2020 - 4:01 am UTC

Version: Oracle 8i

Viewed 50K+ times! This question is

You Asked

Hi,
I am trying to spell the number which is greater than "5373484" . (I tried "select to_char( to_date(5373484,'J'),'Jsp') from dual; " with no Problem).

Is there any functions available for me ?.
Thanks.

and Tom said...

Sure, with a little effort -- you can use the J -> Jsp trick and do it yourself.

tkyte@TKYTE816> create or replace
2 function spell_number( p_number in number )
3 return varchar2
4 as
5 type myArray is table of varchar2(255);
6 l_str myArray := myArray( '',
7 ' thousand ', ' million ',
8 ' billion ', ' trillion ',
9 ' quadrillion ', ' quintillion ',
10 ' sextillion ', ' septillion ',
11 ' octillion ', ' nonillion ',
12 ' decillion ', ' undecillion ',
13 ' duodecillion ' );
14
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 return l_return;
34 end;
35 /

Function created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select
2 spell_number( 12345678901234567890123456789012345678 )
3 from dual;

SPELL_NUMBER(1234567890123456789012345678901234567
--------------------------------------------------
Twelve undecillion Three Hundred Forty-Five decill
ion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillio
n Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quad
rillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundr
ed Forty-Five thousand Six Hundred Seventy-Eight


ops$tkyte@DEV816>
ops$tkyte@DEV816> declare
2 l_num number;
3 l_str varchar2(255);
4 l_spelled varchar2(4000);
5 begin
6 for i in 1 .. 10
7 loop
8 l_num := random.rand() ||
9 random.rand() ||
10 random.rand();
11 l_str :=
12 to_char( l_num, '999,999,999,999,999,999' ) ;
13 l_spelled := spell_number( l_num );
14
15 dbms_output.put_line
16 ( l_str || ' -- ' || l_spelled );
17 end loop;
18 end;
19 /
312,051,345,415,411 -- Three Hundred Twelve
trillion Fifty-One billion Three Hundred
Forty-Five million Four Hundred Fifteen thousand
Four Hundred Eleven
25,131,160,032,468 -- Twenty-Five trillion One
Hundred Thirty-One billion One Hundred Sixty
million Thirty-Two thousand Four Hundred
Sixty-Eight
210,202,025,424,018 -- Two Hundred Ten trillion
Two Hundred Two billion Twenty-Five million Four
Hundred Twenty-Four thousand Eighteen
28,221,169,384,374 -- Twenty-Eight trillion Two
Hundred Twenty-One billion One Hundred Sixty-Nine
million Three Hundred Eighty-Four thousand Three
Hundred Seventy-Four
148,881,759,131,997 -- One Hundred Forty-Eight
trillion Eight Hundred Eighty-One billion Seven
Hundred Fifty-Nine million One Hundred Thirty-One
thousand Nine Hundred Ninety-Seven
178,761,729,314,960 -- One Hundred Seventy-Eight
trillion Seven Hundred Sixty-One billion Seven
Hundred Twenty-Nine million Three Hundred Fourteen
thousand Nine Hundred Sixty
7,789,111,094,989 -- Seven trillion Seven Hundred
Eighty-Nine billion One Hundred Eleven million
Ninety-Four thousand Nine Hundred Eighty-Nine
137,652,698,329,777 -- One Hundred Thirty-Seven
trillion Six Hundred Fifty-Two billion Six Hundred
Ninety-Eight million Three Hundred Twenty-Nine
thousand Seven Hundred Seventy-Seven
155,311,009,417,668 -- One Hundred Fifty-Five
trillion Three Hundred Eleven billion Nine million
Four Hundred Seventeen thousand Six Hundred
Sixty-Eight
139,292,540,822,249 -- One Hundred Thirty-Nine
trillion Two Hundred Ninety-Two billion Five
Hundred Forty million Eight Hundred Twenty-Two
thousand Two Hundred Forty-Nine

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>

followup to comment about 1000

Correct, the code is updated to reflect that. forgot to take into consideration the possibility of 000 as part of a number. We just need to ignore that -- the if statement added takes care of that.

Thanks.

Rating

  (94 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 16, 2001 - 4:21 pm UTC


Helena Markova, April 30, 2001 - 3:31 am UTC


Problem with spell_number function

Nusrat, April 30, 2001 - 5:01 am UTC

the spell_number fuction does not work
when 1000 is passed :
i get the foll. message :-
  1* select spell_number(1000) from dual
SQL> /
select spell_number(1000) from dual
       *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
ORA-06512: at "PAYROLL.SPELL_NUMBER", line 19
ORA-06512: at line 1
 

Excellent

A reader, January 22, 2002 - 5:14 pm UTC

I want to suggest you should make a better search, or clasify to make easy to look in the search archive.

Because I look with
- translate number
- number words
etc.

but I only found this article looking with 'JSP'
Thanks.

Tom Kyte
January 22, 2002 - 6:10 pm UTC

hmm, I don't see how "translate number" would find this, we are not translating a number.

Number words eithers.

But, I always find this one by looking for "spell a number" -- thats what you want to do -- "spell a number".

I don't know how I could have made a search that would find this based on your search strings -- sorry.

I find the interMedia text search to be pretty capable in this area myself.

Better then examples I have seen elsewhere

Jim, January 22, 2002 - 7:44 pm UTC

Liked it better then "spell a number" examples I have seen elsewhere.

FUP to search facility, I think the serach facility is pretty good...

spell sound

A reader, January 23, 2002 - 1:17 am UTC

do you now any facility so i can here a sound for the output

Tom Kyte
January 23, 2002 - 7:13 am UTC

Not a specific one, that would be something specific to your client operating system. I am aware of many tools to convert text to speech....

Excelent SQL Statment

Mohammed Al-moayed, January 23, 2002 - 3:59 am UTC

well, good I like these SQl , which add to my Skills new information , but Colud you please Explain to me what
JSP Stand for

Tom Kyte
January 23, 2002 - 7:26 am UTC

J is the format for a Julian date -- turns a number into a date or vice versa.

  1* select to_char( sysdate, 'j' ) from dual
ops$tkyte@ORA817DEV.US.ORACLE.COM> /

TO_CHAR
-------
2452298

ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_date( 2452299, 'j' ) from dual;

TO_DATE(2
---------
24-JAN-02

sp is a format for SPELLING a julian date:


ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char(sysdate,'jsp') from dual;

TO_CHAR(SYSDATE,'JSP')
------------------------------------------------------------------------------
two million four hundred fifty-two thousand two hundred ninety-eight

I just used that fact to spell much larger numbers then a julian date can hold 

Claudio, January 23, 2002 - 5:51 am UTC


Asif Merchant, January 23, 2002 - 6:45 pm UTC


May be this will be of some use...

create or replace function Digits3_To_Word (P_number in number)
return varchar2 as
-- Pass only three digits
-- This function return word equivalent to its number
-- Accepted values are 0 to 999
--
L_Return2 varchar2(500) := NULL ;

i Number := 0 ;
j Number := 0 ;
k Number := 0 ;

V_inp_Number Number := 0 ;
V_temp Varchar2(50) := NULL;

type OneArray is table of varchar2(50);
type TenArray is table of varchar2(50);
OneStr OneArray := OneArray('One ',
'Two ',
'Three ',
'Four ',
'Five ',
'Six ',
'Seven ',
'Eight ',
'Nine ',
'Ten ',
'Eleven ',
'Twelve ',
'Thirteen ',
'Fourteen ',
'Fifteen ',
'Sixteen ',
'Seventeen ',
'Eighteen ',
'Nineteen ');
TenStr TenArray := TenArray('',
'Twenty ',
'Thirty ',
'Forty ' ,
'Fifty ',
'Sixty ',
'Seventy ',
'Eighty ',
'Ninety ');

BEGIN

V_inp_Number := P_Number ;
if V_inp_number > 999 then
V_inp_number := 0 ;
end if ;

V_temp := LPAD(to_char(V_inp_number),3,0) ;

-- Find Hundredth position
i := to_number(substr(V_temp,1,1));
if i > 0 then
L_Return2 := OneStr(i)||'Hundred ' ;
end if ;

-- Find last 2 digits
i := to_number(substr(V_temp,2,2));
j := to_number(substr(V_temp,2,1));
k := to_number(substr(V_temp,3,1));
if i > 0 and i < 20 then
L_Return2 := L_Return2 || OneStr(i) ;
end if ;
if j >= 2 then
L_Return2 := L_Return2 || TenStr(j) ;
if k > 0 then
L_Return2 := L_Return2|| OneStr(k) ;
end if;
end if;

Return L_Return2 ;
EXCEPTION WHEN OTHERS
THEN NULL ;
END ;
/



create or replace function NUM_TO_WORD (P_inp_num in NUmber)
return Varchar2 as

-- Functions called : Digits3_To_Word

L_Return Varchar2(1000) ;

m Number := 0 ;

V_inp_Num Number := 0 ;
V_inp_Int Number := 0 ;
V_inp_dec Number := 0 ;
V_Char Varchar2(100) ;
V_Temp Number := 0 ;

Type LionArray is table of Varchar2(500) ;
In_str LionArray :=
LionArray ( '',
'Thousand ' ,
'Million ' ,
'Billion ' ,
'Trillion ' ,
'Quadrillion ' ,
'Quintillion ' ,
'Sextillion ' ,
'Septillion ' ,
'Octillion ' ,
'Nonillion ' ,
'Decillion ' ,
'Undecillion ' ,
'Duodecillion ');
Begin
if p_inp_num < 0 then
V_inp_Num := (P_inp_Num * -1) ;
else
V_inp_Num := P_inp_Num;
end if;
V_inp_Int := Trunc(V_inp_Num) ;
V_inp_Dec := V_inp_Num - V_inp_Int ;
if V_inp_Dec > 0 then
V_inp_dec := V_inp_dec * 100 ;
V_inp_Dec := to_number(substr(to_char(V_inp_Dec),1,2)) ;
end if ;

V_char := to_char(V_inp_Int) ;

for m in 1..In_Str.Count Loop
exit when V_char is NULL ;
V_Temp := To_Number(Substr(V_char,(Length(V_char)-2), 3));

if V_temp > 0 then
L_Return := Digits3_To_Word(V_Temp) || In_str(m) || L_Return ;
end if ;

V_char := Substr(V_char,1,(Length(V_Char)-3));

End Loop ;

if V_inp_Int > 0 then
L_Return := 'Dollar '||L_Return ||' and ';
elsif V_inp_Int = 0 then
L_return := 'Dollar Zero and ' ;
end if ;

-- Concatenate the word for decimal digits
if V_inp_Dec > 0 then
L_return := L_return || Digits3_To_Word(V_inp_Dec) ||' Cents';
elsif V_inp_Dec = 0 then
L_Return := L_return ||' Zero Cents';
End if ;
-- -------------------------------

Return L_return;
Exception when others then
return NULL ;
END;
/





Tom Kyte
January 23, 2002 - 9:57 pm UTC

don't see how that is better or even as good as "jSP" with a simple function??

I like to write as little as I have to, to use as much as is builtin as possible.

this:

to_char( to_date( three_digit_number,'J' ), 'JSP' )

just seems so much easier then digits3_to_word doesn't it?







Want more

A reader, January 23, 2002 - 10:57 pm UTC

Hi,

Is it possible to spell all the digits after
decimal point and know how many exact digits
after point? Like:

23456.123423...

Efficient(as you always did) way please.

Thanks

Tom Kyte
January 25, 2002 - 6:52 am UTC

Well, looking at the logic below -- it should be easy for you to accomplish this.

the number of digits after?

greatest(length(x)-instr(x||'.','.'),0)
is one way,

greatest(length(x-trunc(x))-1,0)

another. spelling the decimal part should be easy for you to accomplish depending on the format you want to use. all of the pieces are here for you.

Intelligent query!

Sazzadur Rahman Tusar, January 23, 2002 - 11:58 pm UTC

select to_char( to_date(5373484,'J'),'Jsp') from dual is a very intelligent query to spell out a number.

The above posting...

A reader, January 24, 2002 - 12:56 pm UTC

Compact it is, efficient it is, clever it is, cryptic it most certainly is. Intelligent?. Considering the alternatives, I'd certainly use it though...

Tom Kyte
January 25, 2002 - 8:23 am UTC

Well, if you understand the formats -- cryptic it isn't -- I suppose if you didn't understand what 'J' and 'JSP' formats did, it would be but... isn't:

y += x >> 1;


cryptic? Well, not to a C programmer it isn't. I guess its what you know....

A reader, January 24, 2002 - 4:51 pm UTC

Great !!!!

Does this JSP works for decimals also
ex 23456.66



Tom Kyte
January 25, 2002 - 8:28 am UTC

it works for Julian dates -- you can use it as PART of your solution as I did.

Follow up - Intelligent Query & Decimals

Jim, January 24, 2002 - 5:31 pm UTC

"select to_char( to_date(l_my_variable,'J'),'Jsp')
from dual "

Is fine when the number is less than or equal to 5373484
for numbers bigger then that you will have to use a
solution like Tom's

Re decimals questions - you will have to handle the decimal
yourself. The to_date converts the input to a Julian
date. You will get an error trying to convert
1234.34 to a julian date.
Since the to_char JSP option is julian spell
then I am certain that trying to
to_char(1234.56, 'JSP') will also return an error.
So you will have to split your value into the whole number part and the decimal part.

You could then use the functions to spell the whole
number part and concatenate it with the decimal part.

Fairly straight forward to do in PL/SQL

decimals

Barbara Boehmer, January 24, 2002 - 6:25 pm UTC

Here is a modification of Tom's function to include decimal places, just concatenating the original with the word point followed by each successive digit spelled out:

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> column number_spelled format a55 word_wrapped
SQL> select spell_number( 1234567890123456789.0123456789012345678 )
  2         as number_spelled
  3  from   dual
  4  /

NUMBER_SPELLED
-------------------------------------------------------
One quintillion Two Hundred Thirty-Four quadrillion
Five Hundred Sixty-Seven trillion Eight Hundred Ninety
billion One Hundred Twenty-Three million Four Hundred
Fifty-Six thousand Seven Hundred Eighty-Nine point zero
one two three four five six seven eight nine zero one
two three four five six seven eight


SQL> set serveroutput on
SQL> declare
  2      l_num number;
  3      l_str varchar2(255);
  4      l_spelled varchar2(4000);
  5  begin
  6      for i in 1 .. 5
  7  loop
  8          -- uses random package by Tom Kyte:
  9          l_num := random.rand() ||
 10                   random.rand() ||
 11                   random.rand() ||
 12                   '.'           ||
 13                   random.rand() ||
 14                   random.rand();
 15  
 16          l_str :=
 17              to_char( l_num, '999,999,999,999,999,999.999999999999999999' ) ;
 18          l_spelled := spell_number( l_num );
 19  
 20          dbms_output.put_line
 21          ( l_str ||  ' -- ' || l_spelled );
 22      end loop;
 23  end;
 24  /
12,950,208,415,891.291723034800000000 -- Twelve
trillion Nine Hundred Fifty billion Two Hundred Eight
million Four Hundred Fifteen thousand Eight Hundred
Ninety-One point two nine one seven two three zero
three four eight
2,911,782,023,376.154431611000000000 -- Two trillion
Nine Hundred Eleven billion Seven Hundred Eighty-Two
million Twenty-Three thousand Three Hundred Seventy-Six
point one five four four three one six one one
230,111,308,428,449.206853487000000000 -- Two Hundred
Thirty trillion One Hundred Eleven billion Three
Hundred Eight million Four Hundred Twenty-Eight
thousand Four Hundred Forty-Nine point two zero six
eight five three four eight seven
22,772,324,131,461.593213475000000000 -- Twenty-Two
trillion Seven Hundred Seventy-Two billion Three
Hundred Twenty-Four million One Hundred Thirty-One
thousand Four Hundred Sixty-One point five nine three
two one three four seven five
130,622,360,517,691.106893185700000000 -- One Hundred
Thirty trillion Six Hundred Twenty-Two billion Three
Hundred Sixty million Five Hundred Seventeen thousand
Six Hundred Ninety-One point one zero six eight nine
three one eight five seven

PL/SQL procedure successfully completed.

  

How about other language?

Hing-chau Leung, June 10, 2002 - 10:03 pm UTC

Your solution is excellent. I just wonder is there any similar solution for other language.

Tom Kyte
June 11, 2002 - 10:35 am UTC

No, it only works for english, sorry.

Nice ... but what about UK English?

Charlie, June 11, 2002 - 11:05 am UTC

Here in the UK we deal with numbers slightly differently, inserting the word "and" between the hundreds and tens/units values throughout.

Hence 766,411 is
seven hundred and sixty-six thousand, four hundred and eleven.

Is there any way to do this, or do we have to suffer the rather grating Americanism ;-)

Thank you,

Charlie E.



Tom Kyte
June 11, 2002 - 2:29 pm UTC

Ok, here is the somewhat pedantic but english-ized (or would that be "english-ised") version ;)

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace
  2  function spell_number( p_number in number )
  3  return varchar2
  4  as
  5      type myArray is table of varchar2(255);
  6      l_str    myArray := myArray( '',
  7                             ' thousand ', ' million ',
  8                             ' billion ', ' trillion ',
  9                             ' quadrillion ', ' quintillion ',
 10                             ' sextillion ', ' septillion ',
 11                             ' octillion ', ' nonillion ',
 12                             ' decillion ', ' undecillion ',
 13                             ' duodecillion ' );
 14  
 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 := replace(
 25                           to_char(
 26                             to_date(
 27                              substr(l_num, length(l_num)-2, 3),
 28                                'J' ),
 29                  'Jsp' ) || l_str(i), 'Hundred', 'Hundred AND') || l_return;
 30          end if;
 31          l_num := substr( l_num, 1, length(l_num)-3 );
 32      end loop;
 33  
 34      return l_return;
 35  end;
 36  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> column n format a60
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select
  2  spell_number( 12345678901234567890123456789012345678 ) n
  3  from dual;

N
------------------------------------------------------------
Twelve undecillion Three Hundred AND Forty-Five decillion Si
x Hundred AND Seventy-Eight nonillion Nine Hundred AND One o
ctillion Two Hundred AND Thirty-Four septillion Five Hundred
 AND Sixty-Seven sextillion Eight Hundred AND Ninety quintil
lion One Hundred AND Twenty-Three quadrillion Four Hundred A
ND Fifty-Six trillion Seven Hundred AND Eighty-Nine billion
Twelve million Three Hundred AND Forty-Five thousand Six Hun
dred AND Seventy-Eight


 

Sagi, June 12, 2002 - 3:57 am UTC

Once again thank you tom.

Regards,

Re: Nice ... but what about UK English?

Charlie E, June 12, 2002 - 4:48 am UTC

Well, we English are renowned for being pedantic (well, I am, anyway).

However, there is still a tiny problem with trailing 'AND's wherever there is no tens/units value ...

SQL> select  spell_number(100200) from dual;

SPELL_NUMBER(100200)
-------------------------------------------
One Hundred AND thousand Two Hundred AND

SQL> select spell_number(100) from dual;

SPELL_NUMBER(100)
-------------------------------------------
One Hundred AND

Charlie
 

Tom Kyte
June 12, 2002 - 7:16 am UTC

trivial change:

c/'Hundred', 'Hundred AND'/'Hundred ', 'Hundred AND '/

just add a space

other languages

Barbara Boehmer, June 13, 2002 - 12:42 am UTC

Tom,

I have seen frequent requests on the OTN forums and elsewhere for a function to spell a number in another format or another language. I and various other people have posted various solutions. Most of the ones that I have posted have been based on modifying your original function. I have listed a couple of them below. The first one is a modification of your original function that is intended to spell monetary amounts in Indian format. The second one is a function that converts the output from your function to French. I would imagine that one or the other of these methods could be used to produce output in just about any language. I would be interested in hearing any comments from you (Tom) or any of your readers as to whether there is a better way. Also, if anyone sees any errors in the French words or Indian format, please let me know.

Thanks,
Barbara

create or replace function spell_indian_money
( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( ' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
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' );
end if;
l_num := substr( l_num, 1, length(l_num)-3 );

for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-1, 2) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-1, 2),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-2 );
end loop;

if to_char( p_number ) like '%.%'
then
l_num := substr( round( p_number, 2), instr( p_number, '.' )+1 );
if l_num > 0
then
l_return := l_return || ' And '
|| to_char(
to_date(
l_num,
'J' ),
'Jsp' )
|| ' Paise';
end if;
end if;

return l_return;
end spell_indian_money;
/

create or replace
function spell_number_french( p_number in number )
return varchar2
as
begin
return replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace(
lower( spell_number( p_number ))
, 'duodecillion', 'bidecillion' )
, 'quintillion' , 'cintillion' )
, 'billion' , 'milliard' )
, 'thousand' , 'mille' )
, 'hundred' , 'cent' )
, 'ninety' , 'quatre-vingt-dix')
, 'eighty' , 'quatre-vingt' )
, 'seventy' , 'soixante-dix' )
, 'sixty' , 'soixante' )
, 'fifty' , 'cinquante' )
, 'forty' , 'quarante' )
, 'thirty' , 'trente' )
, 'twenty' , 'vingt' )
, 'nineteen' , 'dix-neuf' )
, 'eighteen' , 'dix-huit' )
, 'seventeen' , 'dix-sept' )
, 'sixteen' , 'seize' )
, 'fifteen' , 'quinze' )
, 'fourteen' , 'quatorze' )
, 'thirteen' , 'treize' )
, 'twelve' , 'douze' )
, 'eleven' , 'onze' )
, 'ten' , 'dix' )
, 'nine' , 'neuf' )
, 'eight' , 'huit' )
, 'seven' , 'sept' )
, 'five' , 'cinq' )
, 'four' , 'quatre' )
, 'three' , 'trois' )
, 'two' , 'deux' )
, 'one' , 'un' )
, 'dix-six' , 'seize' )
, 'dix-cinq' , 'quinze' )
, 'dix-quatre' , 'quatorze' )
, 'dix-trois' , 'treize' )
, 'dix-deux' , 'douze' )
, 'dix-un' , 'onze' )
, '-un ' , '-une ' )
, 'un cent' , 'cent' )
, 'un mille' , 'mille' )
, 'une' , 'un' );
end spell_number_french;
/


Tom Kyte
June 13, 2002 - 8:29 am UTC

Very cool -- thanks. Cannot vouch for the actual output -- the concept is sound

The last word on 'spell the number' (hopefully)

Ed Sleath, June 14, 2002 - 6:22 am UTC

My countryman was a bit off the mark with his analysis. We don't put the "and" in after the hundreds - it's more that we put the "and" in before the tens and units.

So, for example, this year is "two thousand and two".

So, here's another modification to Barbara and Tom's function to allow for this:

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 ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
l_num VARCHAR2(50) DEFAULT ABS(TRUNC(p_number));
l_words VARCHAR2(4000);
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_words := TO_CHAR(TO_DATE(SUBSTR(l_num, LENGTH(l_num) - 2, 3), 'J'), 'Jsp');

IF MOD(l_num, 100) > 0 THEN
l_words :=
SUBSTR(l_words, 1, INSTR(l_words, ' ', -1)) || 'and ' ||
SUBSTR(l_words, INSTR(l_words, ' ', -1) + 1);
END IF;

l_return := l_words || l_str(i) || l_return;

END IF;

l_num := SUBSTR(l_num, 1, LENGTH(l_num) - 3);

END LOOP;

l_return := LTRIM(l_return, 'and ');

IF p_number <= -1 THEN
l_return := 'minus '||l_return;
ELSIF p_number < 0 THEN
l_return := 'zero';
END IF;

RETURN l_return;
END;
/

Frankly, I can't see much practical use for this, but it's an interesting technical exercise.

Incidentally (for Barbara), don't the French say "soixante-et-onze" rather than "soixante-onze"?

Ed


response to Ed Sleath

Barbara Boehmer, June 14, 2002 - 10:37 pm UTC

Ed, 

Thanks for your feedback.  

I think the most common practical usage for spelling numbers in various languages and formats is for automated check writing, especially payroll.  

As to whether to say one hundred one or one hundred and one (as in the movie title "One Hundred and One Dalmations"), I have heard them both ways and seen them both ways.  It may be that both are correct.  I don't know if there is a definitive source that can be consulted.  It may be that which is correct depends on the country or region that you are using it in.

As to whether 71 should be soixante-onze or soixante et onze, I have also seen and heard that both ways.  I found soixante-onze on one website that lists French numbers and soixante et onze on another.  It has been too long since my French classes, for me to remember which way I was taught at the time.  I asked a co-worker from France and she says soixante et onze.  A friend in Canada says soixante-onze.  I have also heard that there may be a difference in the usage depending on whether you are saying 71 things or counting 70, 71, 72, etc.  According to one website, when French is spoken in Switzerland or Belgium, they use an entirely different word for 70.  Once again, lacking a definitive source for clarification, it may be that both are correct, or which is correct is dependent upon the country or region of usage.  

Based on your suggestion, I modified the function to use soixante et onze.  I also made a few other little related corrections below that.  I have listed the corrected spell_number_french function and test below.  However, the spell_number function that is being used within the spell_number_french function is the one that I provided in my response entitled decimals, which does not include the and's, because, if I used the one that includes and's, then I would also have to convert the and's to et's and eliminate them from where they don't belong.  As far as I know, and according to one website, in French, 101 is cent un, not cent et un.

Barbara

create or replace 
function spell_number_french( p_number in number )
return varchar2 
as
begin
    return replace( replace( replace( replace( replace( 
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           replace( replace( replace( replace( replace(
           lower( spell_number( p_number ))
           , 'duodecillion'      , 'bidecillion'     )
           , 'quintillion'       , 'cintillion'      )
           , 'billion'           , 'milliard'        )
           , 'thousand'          , 'mille'           )
           , 'hundred'           , 'cent'            )
           , 'ninety'            , 'quatre-vingt-dix')
           , 'eighty'            , 'quatre-vingts'   )
           , 'seventy'           , 'soixante-dix'    )
           , 'sixty'             , 'soixante'        )
           , 'fifty'             , 'cinquante'       )
           , 'forty'             , 'quarante'        )
           , 'thirty'            , 'trente'          )
           , 'twenty'            , 'vingt'           )
           , 'nineteen'          , 'dix-neuf'        )
           , 'eighteen'          , 'dix-huit'        )
           , 'seventeen'         , 'dix-sept'        )
           , 'sixteen'           , 'seize'           )
           , 'fifteen'           , 'quinze'          )
           , 'fourteen'          , 'quatorze'        )
           , 'thirteen'          , 'treize'          )
           , 'twelve'            , 'douze'           )
           , 'eleven'            , 'onze'            )
           , 'ten'               , 'dix'             )
           , 'nine'              , 'neuf'            )
           , 'eight'             , 'huit'            )
           , 'seven'             , 'sept'            )
           , 'five'              , 'cinq'            )
           , 'four'              , 'quatre'          )
           , 'three'             , 'trois'           )
           , 'two'               , 'deux'            )
           , 'one'               , 'un'              )
           , 'dix-six'           , 'seize'           )
           , 'dix-cinq'          , 'quinze'          )
           , 'dix-quatre'        , 'quatorze'        )
           , 'dix-trois'         , 'treize'          )
           , 'dix-deux'          , 'douze'           )
           , 'dix-un'            , 'onze'            )
           , '-un '              , '-une '           )
           , 'un cent'           , 'cent'            )
           , 'un mille'          , 'mille'           )
           , 'une'               , 'un'              )
           , 'soixante-onze'     , 'soixante et onze')
           , 'quatre-vingts-'    , 'quatre-vingt-'  )
           , '-un'               , ' et un'          )
           , 'quatre-vingt et un', 'quatre-vingt-un');
end spell_number_french;
/

SQL> set serveroutput on
SQL> begin
  2      for i in 1..101
  3      loop
  4          dbms_output.put_line( spell_number_french( i ) );
  5      end loop;
  6  end;
  7  /
un
deux
trois
quatre
cinq
six
sept
huit
neuf
dix
onze
douze
treize
quatorze
quinze
seize
dix-sept
dix-huit
dix-neuf
vingt
vingt et un
vingt-deux
vingt-trois
vingt-quatre
vingt-cinq
vingt-six
vingt-sept
vingt-huit
vingt-neuf
trente
trente et un
trente-deux
trente-trois
trente-quatre
trente-cinq
trente-six
trente-sept
trente-huit
trente-neuf
quarante
quarante et un
quarante-deux
quarante-trois
quarante-quatre
quarante-cinq
quarante-six
quarante-sept
quarante-huit
quarante-neuf
cinquante
cinquante et un
cinquante-deux
cinquante-trois
cinquante-quatre
cinquante-cinq
cinquante-six
cinquante-sept
cinquante-huit
cinquante-neuf
soixante
soixante et un
soixante-deux
soixante-trois
soixante-quatre
soixante-cinq
soixante-six
soixante-sept
soixante-huit
soixante-neuf
soixante-dix
soixante et onze
soixante-douze
soixante-treize
soixante-quatorze
soixante-quinze
soixante-seize
soixante-dix-sept
soixante-dix-huit
soixante-dix-neuf
quatre-vingts
quatre-vingt-un
quatre-vingt-deux
quatre-vingt-trois
quatre-vingt-quatre
quatre-vingt-cinq
quatre-vingt-six
quatre-vingt-sept
quatre-vingt-huit
quatre-vingt-neuf
quatre-vingt-dix
quatre-vingt-onze
quatre-vingt-douze
quatre-vingt-treize
quatre-vingt-quatorze
quatre-vingt-quinze
quatre-vingt-seize
quatre-vingt-dix-sept
quatre-vingt-dix-huit
quatre-vingt-dix-neuf
cent
cent un

PL/SQL procedure successfully completed.
 

Spell Number is Great!!

yendra, September 17, 2002 - 5:57 am UTC

This function is great.I use in purchase order to convert
example 1000000 to one million.
But if i have 10000000.12, it doesn't show me what i want.
I want to show the cent so it show one million and twelve cent.
Can help me???? I need this function.

Tom Kyte
September 17, 2002 - 8:04 am UTC

think about it.

I can spell

1000000 = One Million

I can spell

12 = Twelve


You have 1000000.12 -- so, maybe....

SPELL_NUMBER(TRUNC(:X))||'DOLLARSAND'||SPELL_NUMBER((:X-TRUNC(:X))*100)||'CENTS'
----------------------------------------------------------------------------------------------------
One million dollars and Twelve cents

Its Amazing but not works for -ve values

Kamran Laeeq, October 28, 2002 - 3:02 am UTC

Dear Sir,
Its Amazing and working for any digit except -ve values can any amendment be made to make it useful for -ve intergers also

Tom Kyte
October 28, 2002 - 7:28 am UTC

and what the heck is -ve numbers. (I'm guessing you are making me play a word game and the word you are trying to get me to guess is "negative")

Think about it, what could you do to get it to return Negative One Million. Maybe use abs() on the number when assigning to l_num and conditionally concatenating on the word "negative" or whatever you like at the end?

spell number > 38 digits

ma, April 08, 2003 - 12:18 pm UTC

hello here's my tuppence worth,

the following will spell number > 38 digits and spell point zero at the end

create or replace function spell_number(p_number in varchar2) return varchar2 as
--
type string_array is table of varchar2(255);
--
v_string string_array := string_array('',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ', ' tridecillion ',
' quaddecillion ', ' quindecillion ',
' sexdecillion ', ' septdecillion ',
' octdecillion ', ' nondecillion ',
' dedecillion ');
v_number varchar2(255);
v_return varchar2(4000);
begin
if instr(p_number, '.') = 0 then
v_number := p_number;
else
v_number := substr(p_number, 1, instr(p_number, '.')-1);
end if;
--
if v_number = '0' then
v_return := 'zero';
else
for i in 1 .. v_string.count
loop
exit when v_number is null;
--
if (substr(v_number, length(v_number)-2, 3) <> 0) then
v_return := to_char(to_date(substr(v_number, length(v_number)-2, 3), 'j'), 'jsp') ||
v_string(i) ||
v_return;
end if;
v_number := substr(v_number, 1, length(v_number)-3);
end loop;
end if;
--
-- to include decimal places.
--
if p_number like '%.%' then
v_number := substr(p_number, instr(p_number, '.')+1);
v_return := v_return ||' point';
for i in 1 .. length(v_number)
loop
exit when v_number is null;
if substr(v_number, 1, 1) = '0' then
v_return := v_return ||' zero';
else
v_return := v_return ||' '||
to_char(to_date(substr(v_number, 1, 1), 'j'), 'jsp');
end if;
v_number := substr(v_number, 2);
end loop;
end if; -- include decimal places.
return v_return;
end spell_number;
/

select 11, spell_number('9948443321800876587765443737476566352652356988626255324.0992776467424')
from dual

nine septdecillion nine hundred forty-eight sexdecillion four hundred forty-three quindecillion three hundred twenty-one quaddecillion eight hundred tridecillion eight hundred seventy-six duodecillion five hundred eighty-seven undecillion seven hundred sixty-five decillion four hundred forty-three nonillion seven hundred thirty-seven octillion four hundred seventy-six septillion five hundred sixty-six sextillion three hundred fifty-two quintillion six hundred fifty-two quadrillion three hundred fifty-six trillion nine hundred eighty-eight billion six hundred twenty-six million two hundred fifty-five thousand three hundred twenty-four point zero nine nine two seven seven six four six seven four two four

select 11, spell_number('324.0')
from dual

three hundred twenty-four point zero

select 11, spell_number('.0')
from dual

point zero

spell number > 38 digits version 2

ma, April 08, 2003 - 12:28 pm UTC

sorry, here's an update

create or replace function spell_number(p_number in varchar2) return varchar2 as
--
type string_array is table of varchar2(255);
--
v_string string_array := string_array('',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ', ' tridecillion ',
' quaddecillion ', ' quindecillion ',
' sexdecillion ', ' septdecillion ',
' octdecillion ', ' nondecillion ',
' dedecillion ');
v_number varchar2(255);
v_return varchar2(4000);
begin
if instr(p_number, '.') = 0 then
v_number := p_number;
else
v_number := substr(p_number, 1, instr(p_number, '.')-1);
end if;
--
if v_number = '0'
or v_number is null then
v_return := 'zero';
else
for i in 1 .. v_string.count
loop
exit when v_number is null;
--
if (substr(v_number, length(v_number)-2, 3) <> 0) then
v_return := to_char(to_date(substr(v_number, length(v_number)-2, 3), 'j'), 'jsp') ||
v_string(i) ||
v_return;
end if;
v_number := substr(v_number, 1, length(v_number)-3);
end loop;
end if;
--
-- to include decimal places.
--
if p_number like '%.%' then
v_number := substr(p_number, instr(p_number, '.')+1);
v_return := v_return ||' point';
for i in 1 .. length(v_number)
loop
exit when v_number is null;
if substr(v_number, 1, 1) = '0' then
v_return := v_return ||' zero';
else
v_return := v_return ||' '||
to_char(to_date(substr(v_number, 1, 1), 'j'), 'jsp');
end if;
v_number := substr(v_number, 2);
end loop;
end if; -- include decimal places.
return v_return;
end spell_number;
/

select spell_number('9948443321800876587765443737476566352652356988626255324.0992776467424')
from dual;

> nine septdecillion nine hundred forty-eight sexdecillion four hundred forty-three quindecillion three hundred twenty-one quaddecillion eight hundred tridecillion eight hundred seventy-six duodecillion five hundred eighty-seven undecillion seven hundred sixty-five decillion four hundred forty-three nonillion seven hundred thirty-seven octillion four hundred seventy-six septillion five hundred sixty-six sextillion three hundred fifty-two quintillion six hundred fifty-two quadrillion three hundred fifty-six trillion nine hundred eighty-eight billion six hundred twenty-six million two hundred fifty-five thousand three hundred twenty-four point zero nine nine two seven seven six four six seven four two four

select spell_number('.0')
from dual;

> zero point zero

select spell_number('1')
from dual;

> one

select spell_number('1.0')
from dual

> one point zero


Terrific

Walker, May 08, 2003 - 1:43 pm UTC

Terrific Solution.
Thanks to everyone for their reviews.

spellnum for german language

Maik Rabe, August 14, 2003 - 8:42 am UTC

Hi, 

i have write a stored procedure for all the people, that need this function in german language! The parameter is a number type, so the following is poosible too:

SQL> select spellnum(10/4) from dual;

SPELLNUM(10/4)
---------------------------------------------
Zwei Komma Fünf
(Two Point Five)

CREATE OR REPLACE function spellnum(val in number)
return varchar2
is
v_val varchar2(200);
v_text varchar2(4000);
v_exp_text varchar2(6);
v_teiltext varchar2(200);
commapos pls_integer;
exppos pls_integer;
commasep char;
tsd_stelle pls_integer;
i pls_integer DEFAULT 1;
c char(3);
function get_text(ziffer in varchar2)
return varchar2
is
begin

case trim(ziffer)
    when commasep then return ' Komma';
    when '0' then return ' Null';
    when '1' then return ' Ein';
    when '2' then return ' Zwei';
    when '3' then return ' Drei';
    when '4' then return ' Vier';
    when '5' then return ' Fünf';
    when '6' then return ' Sechs';
    when '7' then return ' Sieben';
    when '8' then return ' Acht';
    when '9' then return ' Neun';
    else return ' Fehler_Text';
    end case;
end;  

function get_hunderter_text(ziffer in varchar2)
return varchar2
is
v_text_hunderter varchar2(150);
v_text_zehner varchar2(150);
v_text_einer varchar2(150);
begin

--Hunderter-Stelle

If (substr(ziffer,1,1)!='0') then
   v_text_hunderter := get_text(substr(ziffer,1,1))||'hundert';

   --wenn nachfolgender Einer/Zehner-Wert, dann 'und' ranhängen
   if (substr(ziffer,2,1) != '0' or substr(ziffer,3,1) != '0') then
         v_text_hunderter := v_text_hunderter||'und';
      end if;
      
   end if;
   
--nun die Einer
if (substr(ziffer,3,1)!='0') then
     v_text_einer := get_text(substr(ziffer,3,1));
     
   --wenn im Bereich 10..19, dann Einer-Text auf 4 Stellen beschneiden (Sieben->Sieb|zehn) 
   if (substr(ziffer,2,1) = '1') then
     v_text_einer := substr(v_text_einer,1,5);
     end if;
     
   --wenn Zehner >=20, dann 'und' zwischen Zehner und Einer einfügen 
   if (substr(ziffer,2,1) not in ('0','1')) then
     v_text_einer := v_text_einer||'und';
     end if;
    
   --aus Ein wird hier Eins, wenn kein Zehner vorhanden
   if (substr(ziffer,2,1) = '0' and substr(ziffer,3,1)='1') then
     v_text_einer := v_text_einer||'s';
     end if;

  end if;

--nun die Zehner
if (substr(ziffer,2,1) != '0') then
   v_text_zehner := substr(get_text(substr(ziffer,2,1)),1,5)||'zig';
   end if;
   
--10 und 20 korrigieren
if (v_text_zehner=' Einzig') then v_text_zehner := ' Zehn'; end if;
if (v_text_zehner=' Zweizig') then v_text_zehner := ' Zwanzig'; end if;
   
--11 und 12 konvertieren
if (v_text_einer||v_text_zehner=' Ein Zehn') then v_text_zehner := ' Elf'; v_text_einer:= null; end if;
if (v_text_einer||v_text_zehner=' Zwei Zehn') then v_text_zehner := ' Zwölf'; v_text_einer:= null; end if;

--Groß/Klein- und Zusammenschreibung abhängig vom vorausgehenden Text einstellen 
if ( v_text_hunderter is not null) then 
   v_text_zehner := trim(lower(v_text_zehner));
   v_text_einer := trim(lower(v_text_einer));
   end if;

if ( v_text_einer is not null) then 
   v_text_zehner := trim(lower(v_text_zehner));
   end if;

return v_text_hunderter||v_text_einer||v_text_zehner;

end;  

function get_tausender_text(stelle in pls_integer, val in pls_integer)
return varchar2
is
ret_text varchar2(100);
begin

  case stelle
    when 0 then return null;
    when 1 then ret_text := ' Tausend';
    when 2 then ret_text := ' Million';
    when 3 then ret_text := ' Milliarde';
    when 4 then ret_text := ' Billion';
    when 5 then ret_text := ' Billiarde';
    when 6 then ret_text := ' Trillion';
    when 7 then ret_text := ' Trilliarde';
    when 8 then ret_text := ' Quadrillion';
    when 9 then ret_text := ' Quadrilliarde';
    when 10 then ret_text := ' Quintillion';
    when 11 then ret_text := ' Quintilliarde';
    when 12 then ret_text := ' Sextillion';
    when 13 then ret_text := ' Sextilliarde';
    when 14 then ret_text := ' Septillion';
    when 15 then ret_text := ' Septilliarde';
    when 16 then ret_text := ' Oktillion';
    when 17 then ret_text := ' Oktilliarde';
    when 18 then ret_text := ' Nonillion';
    when 19 then ret_text := ' Nonilliarde';
    when 20 then ret_text := ' Dezillion';
    when 21 then ret_text := ' Dezilliarde';
        else ret_text := ' mal Zehn hoch'||get_hunderter_text(lpad(3*stelle,3,'0'));
    end case;

if (val > 1) then  
  ret_text := replace(ret_text,'ion','ionen');
  ret_text := replace(ret_text,'iarde','iarden');
  end if;

return ret_text;
end;  

begin

--Null-Wert sofort als 'unbekannt' zuück
if (val is null) then return 'unbekannt'; end if;

--Auslesen des aktuellenDezimalzeichens
commasep:=substr(to_char(1/2),1,1); 

--Wert als Text und commapos bestimmen 
  v_val      := trim(to_char(val));
commapos  := instr(v_val,commasep);

--Bei Exponentialwert Exp-Teil extrahieren
exppos := instr(v_val,'E');
if (exppos > 0) then
  v_exp_text :=substr(v_val, exppos+1);
  v_val := substr(v_val,1,exppos-1);
  v_val := rtrim(v_val,'0');
  end if;

--Betrag auswerten und String anpassen
if (substr(v_val,1,1)='-') then
  v_text := 'Minus';
  v_val     := substr(v_val,2);
  commapos  := commapos - 1;
  end if;

--String auffüllen auf durch 3 teilbare Stellenzahl mit führenden Nullen
if (commapos < 1) then
  v_val := lpad(v_val, (trunc(length(v_val)/3)+1)*3,'0')||commasep;
  else
  v_val      := lpad(v_val,length(v_val)+(trunc((commapos-1)/3)+1)*3-abs(commapos-1),'0');
  end if;

commapos  := instr(v_val,commasep);


--Vorkommastellen
while (i<=commapos-1) loop
  c := substr(v_val,i,3);

  v_teiltext := get_hunderter_text(c);
  if (v_teiltext is not null) then
    tsd_stelle := trunc((commapos-1-i)/3);
    if (tsd_stelle >1 and v_teiltext=' Eins') then
      v_teiltext:=' Eine';
      end if;
    if (tsd_stelle = 1 and v_teiltext=' Eins') then
      v_teiltext:=' Ein';
      end if;
    v_text := v_text||v_teiltext||get_tausender_text(tsd_stelle, to_number(c));
    end if;
  
  i := i + 3;
  
  end loop;


--Vorkomma-Null  
if (v_text is null or v_text = 'Minus') then
  v_text:= trim(v_text||' Null');
  end if;


--Nachkommastellen

--wenn Nachkommastellen vorhanden, dann aufzaehlen 
if (commapos<length(v_val)) then

  --durchlaufen der Nachkommastellen
  for i in commapos .. length(v_Val) loop
    v_teiltext := get_text(substr(v_val, i,1));
    if v_teiltext=' Ein' then v_teiltext:=' Eins'; end if;
    v_text := v_text||v_teiltext; 
    end loop;

  end if;
  
--wenn Exponentialdarstellung, dann exp-Wert angeben  
if (v_exp_text is not null) then

  if (v_text=' Eins') then v_text:=' Ein'; end if;

  v_text := v_text ||' mal Zehn hoch'; 

   --Betrag auswerten und String anpassen
  if (substr(v_exp_text ,1,1)='-') then
    v_text := v_text || ' Minus';
    end if;

  v_text := v_text || get_hunderter_text(substr(v_exp_text,2));
  end if;  

return trim(v_text);

end;
/


Hope, that this procedure will be used!

Maik 

A reader, August 25, 2003 - 6:51 am UTC

No review. Just thanks. well done everyone.
James

Helen, January 28, 2004 - 4:35 am UTC


Can you update the other page?

Tom Best, April 25, 2004 - 8:49 pm UTC

Tom - would you be so kind as to update this:

</code> http://asktom.oracle.com/~tkyte/Misc/SpellANumber.html <code>

to reflect this solution?

Thanks.

Tom Kyte
April 26, 2004 - 6:13 am UTC

there, just made that page, this page :)

number 0 and numbers between 1721058 and 1721423

Laurent, May 19, 2004 - 10:26 am UTC

Hi,
I just learned that the spell method will not work for the non-existant days of the non-existant year 0, that is for the numbers between 1721058 and 1721423

select to_char(to_date( '1721423' , 'J'),'JSP') from dual;
                         *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

To spell numbers between 0 and 86399, you can use seconds of the day :

SQL> select to_char(to_date(0, 'SSSSS'),'Ssssssp') from dual;

TO_C
----
Zero

 

Tom Kyte
May 19, 2004 - 11:08 am UTC

very cool, we'll have to adjust for that.

spell_the_number function works for numbers between 1721058 and 1721423

Barbara Boehmer, May 22, 2004 - 4:15 pm UTC

Laurent,

Although the method that you posted does not work for numbers between 1721058 and 1721423, the spell_the_number function by Tom does:

scott@ORA92> select spell_the_number (1721058) from dual
2 /

SPELL_THE_NUMBER(1721058)
-------------------------------------------------------------------------
One million Seven Hundred Twenty-One thousand Fifty-Eight

scott@ORA92> select spell_the_number (1721423) from dual
2 /

SPELL_THE_NUMBER(1721423)
-------------------------------------------------------------------------
One million Seven Hundred Twenty-One thousand Four Hundred Twenty-Three

Barbara


spell_the_number function works for numbers between 1721058 and 1721423

Laurent Schneider, June 17, 2004 - 9:10 am UTC

Hi Barbara,
I will give you a function for those missing numbers in plain SQL...


SQL> select to_char(to_date('1','J') + 1721300 - 1,'Jsp') from dual;

One Million Seven Hundred Twenty-One Thousand Three Hundred



Regards
Laurent

 

Excellent

Ashwin, July 30, 2004 - 7:04 am UTC

Just awesome guys.Thanks a lot.This helped me a lot.

converting cents into fraction

Yugi, August 19, 2004 - 10:06 pm UTC

hi,

i need a functionality that would convert the number into words but the cents would be in fraction.

example... 1,000.25

output : one thousand & 25/60

Tom Kyte
August 19, 2004 - 10:54 pm UTC

& 25/60?????


umm? huh?  60? 


but anyway, sort of trivial no?

substr( num, instr(num,'.')+1 )  >>> call that X

len(x)+1  >>> that is the number of digits after the "1"


ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec :n := 1000.25
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select substr( :n, instr(:n,'.')+1 ) from dual;
 
SUBSTR(:N,INSTR(:N,'.')+1)
----------------------------------------
25
 
ops$tkyte@ORA9IR2> select substr( :n, instr(:n,'.')+1) || '/1' || rpad('0',length(substr( :n, instr(:n,'.')+1)),'0')
  2  from dual;
 
SUBSTR(:N,INSTR(:N,'.')+1)||'/1'||RPAD('0',LENGTH(SUBSTR(:N,INSTR(:N,'.')+1)),'
-------------------------------------------------------------------------------
25/100
 


just use the last one, concat onto the rest.  
 

follow up on 25/100

Yugi, August 19, 2004 - 11:47 pm UTC

hi,

i'm sorry its 2/100 not 25/60.... can you pls post the exact script so i could arrive at this result

1000.25

one thousand & 25/100 only

thanks again

Tom Kyte
August 20, 2004 - 10:39 am UTC

ummm

spell_number(trunc(:n)) || ' & ' || substr( :n, instr(:n,'.')+1) || '/1' ||
rpad('0',length(substr( :n, instr(:n,'.')+1)),'0')

just what I said - concat the xx/100 with spell_number?

A reader, August 20, 2004 - 4:43 am UTC


hi,

closing thread i got it.....


thanks

hi

A reader, August 20, 2004 - 5:16 am UTC


hi all,

my mistake..i still have problem..pls post the whole script

thanks.

hi

YUgi, August 22, 2004 - 8:33 pm UTC

sorry for the confusion....i got it...thanks for the time

Can reverse way is possible ? ( words to number )

pjp, August 26, 2004 - 2:14 am UTC

Hi Tom,

thanks for your great answers as usual. I have executed following SQL

1* select to_char(to_Date(5373484, 'J'), 'JSP') from dual
11:31:17 atlas@ATP1P1> /

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR

I am just wondering that can we arrive to the number by reading words ( Only in SQL not in PL/SQL )

select to_number(to_char('FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR'), 'J') ...
etc.

so output will show me 5373484

thanks & regards
pjp

Tom Kyte
August 26, 2004 - 9:43 am UTC

believe you will be writing code for that yourself. there is no tricky format I know of to turn words into numbers.

math

ho, September 15, 2004 - 7:34 pm UTC

thirty two and sixty seven thousand ninety four hundred thousanaths

math

ho, September 15, 2004 - 7:35 pm UTC

thirty two and sixty seven thousand ninety four hundred thousanaths

RE: Can reverse way is possible ? ( words to number )

Laurent Schneider, November 04, 2004 - 8:34 am UTC

either write your own pl/sql function, or do a loop in sql

select r from (select rownum r from (select 1 from dual group by cube(1,1,1,1,1,1,1,1,1,1,1)),(select 1 from dual group by cube(1,1,1,1,1,1,1,1,1,1,1,1))
where rownum < date '9999-12-31' - date '-4712-01-01')
where to_char(date '-4712-01-01' + (r-1),'JSP') = 'FOUR MILLION FOUR HUNDRED FORTY-FOUR THOUSAND FOUR HUNDRED FORTY-FOUR';

R
----------
4444444




wow

doug, November 16, 2004 - 8:18 pm UTC

RE: RE: Can reverse way is possible ? ( words to number )

That's some brilliant code, it's too bad that running it takes so long. (Try running it to translate 'FOUR')

Great use / grasp of sql though, yeesh.

Ask Tom

maryah, November 25, 2004 - 8:18 pm UTC

Not useful at all!
Because-too complicated
-hard to understand with all the ( : ; ) = -
-no information
-web page not designed well (eg. no color!)

Tom Kyte
November 25, 2004 - 8:25 pm UTC

but gosh and golly batman, it actually answered the question.

My favorite font: courier

If I had my way, this would be white on blue/black but this seems to be the internet standard "black on white" (what the HECK does color have to do with CONTENT... geez)

too complicated -- hmmm. ok -- you must not write code for money.

sorry, just feeling slightly sarcastic tonight but hey, there you go.


I sort of thought "function over form" -- you will not see blinding colors here, animated gifs are totally outlawed. This site is about technical information. If you want pretty colors and flashing lights -- Las Vegas is for you.


but -- have a nice day, happy thanksgiving and all!



Oh well....

Gj, November 26, 2004 - 3:59 am UTC

I guess some people simply want to be spoon-fed all the answers with nice pretty lights included!

Oh well, you can't please everyone I suppose!

Tom Kyte
November 26, 2004 - 9:31 am UTC

yeah, the comment about "no colors" really made me laugh. The person sort of totally "misses the point"

Hey there!

Victoria Belmont, January 31, 2005 - 6:30 pm UTC

Hey there this was a very useful site! thanx sooooooooooo much!

er5yujh5rl

w5uwr6, February 08, 2005 - 11:07 pm UTC

5yeyttyrtuy etuw46ue6u et6yuie56i

Tom Kyte
February 09, 2005 - 2:45 am UTC

^@$@$GRQtrqgfa

I feel like I'm commicating now....

Thanks...

Kiran Shah, March 21, 2005 - 4:05 am UTC

Thanks to Tom and
Special thanks to Barbara Boehmer for her function spell_indian_money. I truly benefitted from this.


modification as under for indian version

Nikunj Thaker, April 11, 2005 - 2:30 am UTC

Dear Barbara,

In you code little change is required in indian function spell_indian_money b'coz if you see the below result.

SQL> select spell_indian_money(8.02) from dual;

SPELL_INDIAN_MONEY(8.02)
--------------------------------------------------------------------------------
Eight And Two Paise

SQL> select spell_indian_money(8.20) from dual;

SPELL_INDIAN_MONEY(8.20)
--------------------------------------------------------------------------------
Eight And Two Paise

if you see both resulting same but at actual 8.20 should return 

SQL> select nspell_indian_money(8.02) from dual;

NSPELL_INDIAN_MONEY(8.02)
--------------------------------------------------------------------------------
Eight And Two Paise

SQL> select nspell_indian_money(8.20) from dual;

NSPELL_INDIAN_MONEY(8.20)
--------------------------------------------------------------------------------
Eight And Twenty Paise

So i had modified your code as below.

create or replace function spell_indian_money 
    ( p_number in number )
    return varchar2
as
    type myArray is table of varchar2(255);
    l_str    myArray := myArray( ' Thousand ', 
                                 ' Lakh ',
                                 ' Crore ', 
                                 ' Arab ',
                                 ' Kharab ', 
                                 ' Shankh ' );
    l_num varchar2(50) default trunc( p_number );
    l_return varchar2(4000);
    d_count number;
begin
    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' );
    end if;
    l_num := substr( l_num, 1, length(l_num)-3 );

    for i in 1 .. l_str.count
    loop
        exit when l_num is null;
        if ( substr(l_num, length(l_num)-1, 2) <> 0 )
        then
            l_return := to_char(
                            to_date(
                             substr(l_num, length(l_num)-1, 2),
                               'J' ),
                        'Jsp' ) || l_str(i) || l_return;
        end if;
        l_num := substr( l_num, 1, length(l_num)-2 );
    end loop;

    if to_char( p_number ) like '%.%'
    then
        l_num := substr( round( p_number, 2), instr( p_number, '.' )+1 );

/* here i had done my modifcation */
    if (length(substr( round( p_number, 2), instr( p_number, '.' )+1)))=1 then
        l_num:=to_number(to_char(l_num)||'0');
    end if;
/* here i had completed my modifcation */
        if l_num > 0
        then
            l_return := l_return || ' And '
            || to_char(
                   to_date(
                    l_num,
                     'J' ),
               'Jsp' )
            || ' Paise';
        end if;
    end if;

    return l_return;
end spell_indian_money;
/

Regards,
Nikunj. 

English question

Andrea Foucher, April 25, 2005 - 10:57 am UTC

Is this the correct way to write "a hundred and sixty-nine thousand one hundred and sixty-seven" if I needed to spell it out?

Tom Kyte
April 25, 2005 - 1:28 pm UTC

ops$tkyte@ORA10G> select to_char( to_date(169167,'j'),'jsp') from dual;
 
TO_CHAR(TO_DATE(169167,'J'),'JSP')
-------------------------------------------------------
one hundred sixty-nine thousand one hundred sixty-seven


one hundred, not "a hundred" 

RE: RE: Can reverse way is possible ? ( words to number )

Laurent Schneider, April 26, 2005 - 9:14 am UTC

> it's too bad that running it takes so long
ok, here is one more try

select sp, n from (select 'FIVE THOUSAND ONE' sp from dual)
model
dimension by (1 dim)
measures (0 n, sp)
rules iterate (10000) until (to_char(date '2000-01-01' + (ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
( n[1]=ITERATION_NUMBER )
/

SP N
----------------- ----------
FIVE THOUSAND ONE 5001

Elapsed: 00:00:00.11


Tom Kyte
April 26, 2005 - 9:29 am UTC

Interesting approach, I used connect by, should work up to the number of seconds in the day. 

ops$tkyte@ORA10G> variable x varchar2(100)
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
  2     select to_char( to_date( 123, 'J' ), 'JSP' ) into :x from dual;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select max(level)+1
  2  from dual
  3  connect by level < 86400 and to_char(trunc(sysdate)+level/86400,'SSSSSSP') <> :x
  4  /
 
MAX(LEVEL)+1
------------
         123
 
Elapsed: 00:00:00.05
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select sp, n from (select :x sp from dual)
  2     model
  3     dimension by (1 dim)
  4     measures (0 n, sp)
  5     rules iterate (86400) until (to_char(date '2000-01-01' +
  6  (ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
  7    ( n[1]=ITERATION_NUMBER )
  8  /
 
SP                                      N
------------------------------ ----------
ONE HUNDRED TWENTY-THREE              123
 
Elapsed: 00:00:00.06
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> begin
  2     select to_char( to_date( 86399, 'J' ), 'JSP' ) into :x from dual;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select max(level)+1
  2  from dual
  3  connect by level < 86400 and to_char(trunc(sysdate)+level/86400,'SSSSSSP') <> :x
  4  /
 
MAX(LEVEL)+1
------------
       86399
 
Elapsed: 00:00:00.83
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select sp, n from (select :x sp from dual)
  2     model
  3     dimension by (1 dim)
  4     measures (0 n, sp)
  5     rules iterate (86400) until (to_char(date '2000-01-01' +
  6  (ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
  7    ( n[1]=ITERATION_NUMBER )
  8  /
 
SP                                      N
------------------------------ ----------
EIGHTY-SIX THOUSAND THREE HUND      86399
RED NINETY-NINE
 
 
Elapsed: 00:00:01.04
ops$tkyte@ORA10G>
 

RE: RE: Can reverse way is possible ? ( words to number )

Laurent Schneider, April 27, 2005 - 5:04 am UTC

great piece of code :-)

correction of Indian version

Barbara Boehmer, April 30, 2005 - 2:00 pm UTC

Nikunj Thaker,

Thanks for pointing out the error. The following corrects the problem by just adding rpad to the original code.


create or replace function spell_indian_money
( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( ' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
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' );
end if;
l_num := substr( l_num, 1, length(l_num)-3 );

for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-1, 2) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-1, 2),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-2 );
end loop;

if to_char( p_number ) like '%.%'
then
-- correction made in line below by adding rpad:
l_num := rpad (substr (round (p_number, 2), instr (p_number, '.' ) + 1), 2, '0');
if l_num > 0
then
l_return := l_return || ' And '
|| to_char(
to_date(
l_num,
'J' ),
'Jsp' )
|| ' Paise';
end if;
end if;

return l_return;
end spell_indian_money;
/


sufficient

Hakim.Zen, June 03, 2005 - 4:43 pm UTC

Too much code, it can be done with 1/2 as much
my solution is superior

Tom Kyte
June 03, 2005 - 5:36 pm UTC

might be superior, but it sure is "missing"

Check this out

Yasser Qureshi, June 29, 2005 - 3:40 am UTC

Hi.
I have done following for the problem. Useful for Pakistan & India. Can be modified according to any language. You can add code to spell a number upto any range.

CREATE OR REPLACE FUNCTION DO_SPELL (NUM IN NUMBER)
RETURN VARCHAR2
IS
CH VARCHAR2(200):='';
BEGIN
IF VALUE_IS_ZERO(NUM)='TRUE'
THEN RETURN '/- Rs Only';
ELSE
IF LENGTH(NUM)=8 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||SUBSTR(NUM,1,1)),'YSP')||' Crore ');
CH:=CH||DO_SPELL(SUBSTR(NUM,2));
ELSIF LENGTH(NUM)=7 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||SUBSTR(NUM,1,2)),'YYSP')||' Lac ');
CH:=CH||DO_SPELL(SUBSTR(NUM,3));
ELSIF LENGTH(NUM)=6 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||SUBSTR(NUM,1,1)),'YYSP')||' Lac ');
CH:=CH||DO_SPELL(SUBSTR(NUM,2));
ELSIF LENGTH(NUM)=5 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||SUBSTR(NUM,1,2)),'YYSP')||' Thousand ');
CH:=CH||DO_SPELL(SUBSTR(NUM,3));
ELSIF LENGTH(NUM)=4 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||NUM),'YYYYSP'));
CH:=CH||DO_SPELL(0);
ELSIF LENGTH(NUM)=3 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||NUM),'YYYSP'));
CH:=CH||DO_SPELL(0);
ELSIF LENGTH(NUM)=2 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||NUM),'YYSP'));
CH:=CH||DO_SPELL(0);
ELSIF LENGTH(NUM)=1 THEN
CH:=INITCAP(TO_CHAR(TO_DATE('30-JUL-'||NUM),'YSP'));
CH:=CH||DO_SPELL(0);
END IF;
END IF;
RETURN CH;
END;

CREATE OR REPLACE FUNCTION VALUE_IS_ZERO (NUM IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
IF NUM=0 THEN
RETURN 'TRUE';
ELSE
RETURN 'FALSE';
END IF;
END;

/* where Crore is equal to 10 Million and Lac is equal to one hundred thousand. */

Select Do_spell(454530) from dual;

how do u spell 40

Hassan, July 08, 2005 - 4:39 pm UTC

how do u spell 40

Tom Kyte
July 08, 2005 - 6:33 pm UTC

You would have to find "U" and ask "U" how "U" spells forty

RE: check this out

A reader, July 22, 2005 - 9:20 am UTC

Interresting to spell years...

SQL>  select to_char(date '9999-01-01','SYYYYSP') from dual;

TO_CHAR(DATE'9999-01-01','SYYYYSP')
--------------------------------------
NINE THOUSAND NINE HUNDRED NINETY-NINE

SQL> select to_char(date '0000-01-01','SYYYYSP') from dual;

TO_CHAR(DATE'0000-01-01','SYYYYSP')
-------------------------------------------
0000000000000000000000000000000000000000000

SQL> select to_char(date '-4712-01-01','SYYYYSP') from dual;
select to_char(date '-4712-01-01','SYYYYSP') from dual
       *
ERROR at line 1:
ORA-01801: date format is too long for internal buffer
 

Algorisms: Writing Large Numbers

Jenaya, August 28, 2005 - 8:05 am UTC

im not sure how to make these numbers into numerals. these are the ones i am unsure of:::
(a) 28 million, 5 thousand and seventy-four
(b) thirty-four thousand and one
(c) 7 million, five hundred and three
(d) 729 thousand, 106
(e) 50 000 + 400+ 7
(f) 8 000 000 + 5000 + 85
(g) 22 000 + 80 +4

Tom Kyte
August 28, 2005 - 8:34 am UTC

not sure what you mean? You have written the large numbers already.

hahahahahaha............

Jay, September 01, 2005 - 10:36 am UTC

Hey Tom,

Hope you are doing well.

I was laughing for ten minutes Non-Stop after reading this one. You can be really hilarious at times Tom!! Way to go :-)

------------------------------------------------------------

Followup:

You would have to find "U" and ask "U" how "U" spells forty

90, October 03, 2005 - 3:26 am UTC

90-9

Number to Words

Rajni Kant Tahlyan, October 26, 2005 - 4:26 pm UTC

This is a very useful page, especially for the people working in projects related to finance. These guys usually needs to work on files to send the money figures and that too in both numbers and words. Here comes this page very helpful.

slightly varied problem

Asm Naveed, November 11, 2005 - 5:01 am UTC

I have slightly varied problem.
Given a number n, I want to know how many

1- How many millions are in it.
2- How many 100 Thousands are in the reaaining no. i.e. (
how many 100 thousands are in the no. n - (no. of millions in n)
3- How many 10 Thousands are in the remaining no.
4- Thousands are in the remaining no.
5- Hunderes are in the remaining no.
6- Tens are in the remaining no.
7- How many units are in the reamining no.
For e.g. the number
4752524694
Has
Four Thousand seven hundered and fifty two --Million in it.
Five ---Hundered Thousands in it.
Two ----Ten Thousands in it.
Four ---- Thousands in it.
Six ---- Hundereds in it.
Nine ---- Tens in it.
Four ---- Units in it.

Can there by a function which can return me these words
delemited by any delemeter.

The maximum number will be of 10 digits only.

Thanks and Regards,



Tom Kyte
November 12, 2005 - 8:19 am UTC

give it is go - you see the techniques available to you - simple exercise in programming this one is.

(looks like "substr" to me, doesn't it)


Spell the number

YM Leong, December 27, 2005 - 7:48 pm UTC

To Tom:

I don't see "Hundred", "One", "Two" etc being initialised into the array, but results can produce the word "Hundred"?

Lost

See array values below:
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );


Tom Kyte
December 28, 2005 - 9:21 am UTC

I'm using to_char to spell out anything less than one thousand - we substr off upto three digits - spell them - then add the thousand/million/etc as needed.

write the number

matty, February 06, 2006 - 3:40 am UTC

how do you do this in numbers sixty-four thousand, nine hundred and twenty-eight

Tom Kyte
February 07, 2006 - 12:33 am UTC

not sure what you mean? are you just asking for:

64,928
?

write the number

matty, February 06, 2006 - 3:42 am UTC

how do you do this in numbers sixty-four thousand, nine hundred and twenty-eight

Words to numbers?

Neil, February 07, 2006 - 5:01 am UTC

Matty,
Do you want to change words into numbers? I've never done it myself, but here's a couple of thoughts:
I'd get rid of all the unnecessary stuff first like commas and "and". I think you could get rid of tokens like "million" and "thousand" as well.
When you see the word "twenty" output a 2, if you see the word "twenty-seven" output a 2 and then a 7. One hundred and thrity nine? Output a 1 for the "one", ignore the "hundred" because the subsequently found words will multiply the previous by 10 thus preserving the magnitude, a 3 for the "thirty" and a 9 for the "nine". Stash the words in a pl/sql table indexed by varchar2 along with their eqivalents which you will print out. Thus the token "thrty" is looked up using the construct

dbms_output.put_line(numbers(token).digit);

where token is the string "thirty" and the digit returned would be 3.
If the token has a hyphen in it, then look up the substrings to the left and then to the right. You will probably have to look out for "dollars" and "cents" and the decimal point, but it shouldn't be too hard.
As I've said, this is just off the top of my head, and may crash and burn...


Victoria Curry, August 28, 2006 - 11:00 pm UTC

USE DIGITS TO WRITE THIS NUMBER: TEN BILLION, TWO HUNDRED FIVE MILLION , FORTY-ONE

Tom Kyte
August 29, 2006 - 7:02 am UTC

WHY? CAN'T YOU?

ops$tkyte%ORA10GR2> select spell_number( 10205000041 ) from dual;

SPELL_NUMBER(10205000041)
-------------------------------------------------------------------------------
Ten billion Two Hundred Five million Forty-One
 

Add comment in the table

senthil, October 13, 2006 - 8:24 am UTC

i am create table
that table having some column, now i want column_name,datatype and adding comment how is possible?

example:

create table emp
(empno number /*this empno /*);

now see the comment:
desc dict_columns;
senthil.k

Tom Kyte
October 13, 2006 - 2:26 pm UTC

won't happen, doesn't work that way.

You use the COMMENT sql command to comment on tables and columns.

Indian Currentcy in words

Manjunath, February 15, 2007 - 5:31 am UTC

Excellent job..hats off all of u..

this code is awesome..Thank u

Number Spelling in Portuguese

Nuno Farinha, August 23, 2007 - 10:13 am UTC

For those needing, like me, this function for portuguese
language. Note that like in french language we use the
billion, trillion, etc on a 10^6 basis. This differs from the english language that uses it in a 10^3 basis.

create or replace
function spell_number( p_number in number)
return varchar2 is

--------------------------------------------------------------
-- NFarinha 2007/08/23 based on
-- ask tom : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650
-- Portuguese number spelling
--------------------------------------------------------------

type myArray is table of varchar2(255);

l_str_pt myArray := myArray( '',
' milh',
' bili',
' trili',
' quatrili',
' quintili',
' sextili',
' septili',
' octili',
' nonili');

l_num varchar2(50) default trunc( p_number );
l_curr_num_uni varchar2(50);
l_curr_num_mil varchar2(50);
l_return varchar2(4000);

function spell_hundreds(p_val number) return varchar2 is
retval varchar2(100);
begin
select des
into retval
from
(select centenas.val*100 + dezenas.val val,
case when centenas.val = 0 then null
when centenas.val*100 + dezenas.val = 100 then 'cem'
else centenas.des end||
case when centenas.val != 0 and dezenas.val != 0 then ' e ' end||
decode(dezenas.val ,0,null,dezenas.des) des
from
(select rownum-1 val,column_value des from table(varchar2_table_100('','cento','duzentos','trezentos','quatrocentos','quinhentos','seiscentos','setecentos','oitocentos','novecentos'))) centenas,
(select
dezenas.val*10 + unidades.val val,
decode(dezenas.val ,0,null,dezenas.des)||
case when dezenas.val != 0 and unidades.val != 0 then ' e ' end||
unidades.des des
from
(select decode(rownum,1,0,rownum) val,column_value des from table(varchar2_table_100('','vinte','trinta','quarenta','cinquenta','sessenta','setenta','oitenta','noventa'))) dezenas,
(select rownum - 1 val,column_value des from table(varchar2_table_100('','um','dois','três','quatro','cinco','seis','sete','oito','nove'))) unidades
union all
select rownum + 9 val,
column_value des
from table(varchar2_table_100('dez','onze','doze','treze','catorze','quinze','dezasseis','dezassete','dezoito','dezanove')) excep) dezenas)
where val = p_val;

return retval;
exception
when others then
return null;
end;

begin
if l_num = 0 then
return 'zero';
end if;

for i in 1 .. l_str_pt.count
loop
exit when l_num is null;

l_return := case
when instr(l_return,' e ',-1) = 0 then ' e '
when l_curr_num_mil <> 0 or l_curr_num_uni <> 0 then ', '
end||
l_return;

l_curr_num_uni := nvl(substr(l_num, -3), l_num);
l_curr_num_mil := nvl(substr(l_num, -6), l_num);
l_curr_num_mil := nvl(substr(l_curr_num_mil, 1, length(l_curr_num_mil)-3 ),0);

if ( l_curr_num_uni <> 0 or l_curr_num_mil <> 0)
then
l_return := case when l_curr_num_uni <> 0 then spell_hundreds(l_curr_num_uni) end||
l_str_pt(i)||
case when l_str_pt(i) is not null and
l_curr_num_mil = 0 and
l_curr_num_uni = 1 then 'ão'
when l_str_pt(i) is not null then 'ões'
end||
l_return;
end if;

if ( l_curr_num_mil <> 0 )
then
l_return := case when l_curr_num_mil > 1 then spell_hundreds(l_curr_num_mil)||' ' end||
'mil'||
case when l_curr_num_uni between 1 and 100 then ' e '
when l_curr_num_uni <> 0 then ' '
end||
l_return;
end if;

l_num := substr( l_num, 1, length(l_num)-6 );
end loop;

return l_return;

exception
when others then
return null;
end;

Good work Everyone

Sharon, February 13, 2008 - 3:29 am UTC

Hi Yasser Qureyshi,
I tried your query, the results are
>Select Do_spell(454530) from dual;
DO_SPELL(454530)
--------------------------------------------------------------------------------
Four Lac Fifty-Four Thousand Five Hundred Thirty/- Rs Only
> Select Do_spell(40254530) from dual;
DO_SPELL(40254530)
--------------------------------------------------------------------------------
Four Crore Two Lac Fifty-Four Thousand Five Hundred Thirty/- Rs Only
> Select Do_spell(440254530) from dual;
DO_SPELL(440254530)
--------------------------------------------------------------------------------

Not displaying any answer as the value is more than 5373484 julian limit so Tom's and Barbara's solutions are better.

Thank you

A reader, November 06, 2009 - 4:16 am UTC


Asif Bin Qadir, December 10, 2009 - 8:21 am UTC

Thank you very much Tom,

Your SPELL_NUMBER function is working fine.

Thanks again.

spell it

preston, December 10, 2009 - 12:29 pm UTC

can you please spell this 15765648753476486376374765376537373735763754x10^32
Tom Kyte
December 10, 2009 - 3:20 pm UTC

supply myArray values and we'd be glad to :)

Google is your friend

Mike Kutz, December 10, 2009 - 5:36 pm UTC

I like free info, but someone else will have to validate that it is correct. It appears that anything over 10^48 does not have a 'standard' naming convention.

http://en.wikipedia.org/wiki/Names_of_large_numbers

(perl an awk are also your friends)
Following built from the "lower" table. (not the "Standard Dictionary" table)

l_str myArray := myArray( '',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion ',
' tredecillion ',
' quattuordecillion ',
' quindecillion ',
' sexdecillion ',
' septendecillion ',
' octodecillion ',
' novemdecillion ',
' vigintillion ',
' unvigintillion ',
' duovigintillion ',
' tresvigintillion ',
' quattuorvigintillion ',
' quinquavigintillion ',
' sesvigintillion ',
' septemvigintillion ',
' octovigintillion ',
' novemvigintillion ',
' trigintillion ',
' untrigintillion ',
' duotrigintillion ',
' trestrigintillion ',
' quattuortrigintillion ',
' quinquatrigintillion ',
' sestrigintillion ',
' septentrigintillion ',
' octotrigintillion ',
' noventrigintillion ',
' quadragintillion ' );




Neat/cool alternative: FFSP format

Martijn Hoekstra, January 11, 2010 - 1:00 pm UTC

I got this alternative via OTN, using TO_CHAR and TO_TIMESTAMP:
http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=1011099

This article provides more details and the restrictions one might encounter:
http://www.sqlsnippets.com/en/topic-12355.html
( and the article references this thread as well ;-) )

and thought it might be useful to mention it over here as well.

SQL> with sample_data as ( select  power(level, level) num
  2                        from dual
  3                        connect by level <= 8
  4                      )
  5  --
  6  --
  7  --                    
  8  select num
  9  ,      to_char( to_timestamp( lpad( num, 9, '0'), 'FF9' ), 'Ffsp' ) words1
 10  --,      to_char( to_timestamp( lpad( num, 9, '0'), 'FF9' ), 'Ffspth' )  words2
 11  from   sample_data
 12  /

       NUM WORDS1
---------- ------------------------------------------------------------------------------
         1 One
         4 Four
        27 Twenty-Seven
       256 Two Hundred Fifty-Six
      3125 Three Thousand One Hundred Twenty-Five
     46656 Forty-Six Thousand Six Hundred Fifty-Six
    823543 Eight Hundred Twenty-Three Thousand Five Hundred Forty-Three
  16777216 Sixteen Million Seven Hundred Seventy-Seven Thousand Two Hundred Sixteen


Imo the 'coolness-factor' here is quite large :-)

Regards,
Martijn
Tom Kyte
January 18, 2010 - 12:16 pm UTC

nice, thanks for the update :)

always good to see things evolve over time, change...

spelling numbers

Jeffrey Lomba, April 16, 2010 - 9:05 pm UTC

spell 983,167,331,215
Tom Kyte
April 17, 2010 - 12:50 pm UTC

ops$tkyte%ORA10GR2> select spell_number( to_number( '983,167,331,215', '999,999,999,999' ) ) from dual;

SPELL_NUMBER(TO_NUMBER('983,167,331,215','999,999,999,999'))
-------------------------------------------------------------------------------
Nine Hundred Eighty-Three billion One Hundred Sixty-Seven million Three Hundred
 Thirty-One thousand Two Hundred Fifteen



was there a problem?

spell number

A reader, May 19, 2010 - 8:04 am UTC

spell number in spanish. Please let us know the approach for it.

Tom Kyte
May 24, 2010 - 10:40 am UTC

ctl-f for

other languages

on this page, you'll see how people have done it for other languages.

A reader, September 09, 2010 - 9:27 pm UTC

ninety five billion, twenty three million, eleven
Tom Kyte
September 13, 2010 - 6:51 am UTC

nope, you guessed wrong, off by 42

bernice, October 31, 2011 - 1:53 pm UTC

can you write this in figures for me pls ten billon,seventy two millon,six hundred and seventeen thousand,two hundred and eight :D thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thxthx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx thx :D

IF the NLS_CALENDAR is not GREGORIAN

Sridhar, November 03, 2011 - 5:44 am UTC

Hi Tom,
If the NLS_CALENDAR is not set to GREGORIAN, "Spell the number" code fails. We have a code in product to spell out numbers, but when we shipped to Dubai, where they use Arabic Hijrah Calendar it failed:
alter session set nls_calendar='GREGORIAN';
select to_char(to_date(20,'J'),'JSP') from dual
Output : TWENTY

alter session set nls_calendar='Arabic Hijrah';
select to_char(to_date(20,'J'),'JSP') from dual
ERROR : ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Is there a way to spell out numbers if the calendar is NOT gregorian??
Many Thanks.
Sri
Tom Kyte
November 03, 2011 - 5:57 am UTC

well, you sort of have the technique here.

switch the nls_calendar in the function and put it back at the end.

nls_session_parameters has the current nls_calendar, read it out - if not gregorian - set it and then put it back later.

ORA-01841 but still a feedback

Sokrates, November 03, 2011 - 8:13 am UTC

sokrates@11.2.0.2 > set feedback on
sokrates@11.2.0.2 > alter session set nls_calendar='Arabic Hijrah';

Session altered.

sokrates@11.2.0.2 > select to_date(20,'J') from dual;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


no rows selected



Question:

My query encounters an exception - ok.
But: why do I get a feedback "no rows selected" from sqlplus then ?
I didn't expect that.

See:
sokrates@11.2.0.2 > alter session set nls_calendar='Gregorian';

Session altered.

sokrates@11.2.0.2 > select to_date('2011-11-31', 'yyyy-mm-dd') from dual;
select to_date('2011-11-31', 'yyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified


sokrates@11.2.0.2 >


no "no rows selected" here.

a bug ?
(
btw: I didn't dare to post what I get when issuing "select to_date('2011-11-31', 'yyyy-mm-dd') from dual" against "nls_calendar='Arabic Hijrah'"
)
Tom Kyte
November 03, 2011 - 9:51 am UTC

ok, this is whacky:

ops$tkyte%ORA11GR2> alter session set nls_calendar='Arabic Hijrah';

Session altered.

ops$tkyte%ORA11GR2> select to_date(20,'J') from dual;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



no rows selected


ops$tkyte%ORA11GR2> select * from dual where to_date(20,'J') < sysdate;

D
-
X


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          for x in (select to_date(20,'J') x from dual) loop dbms_output.put_line( 'hello world ' || x.x ); end loop;
  3  end;
  4  /
        for x in (select to_date(20,'J') x from dual) loop dbms_output.put_line( 'hello world ' || x.x ); end loop;
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at line 2


ops$tkyte%ORA11GR2> begin
  2          for x in (select to_date(20,'J') x from dual) loop dbms_output.put_line( 'hello world ' ); end loop;
  3  end;
  4  /
hello world

PL/SQL procedure successfully completed.



apparently, it must be evaluating some of the NLS stuff after it returns from the database - notice how plsql doesn't die until you reference the selected attribute.

if you look at the trace for the failed one:

=====================
PARSING IN CURSOR #3914836 len=119 dep=0 uid=374 oct=47 lid=374 tim=1320351618864611 hv=2209841403 ad='20c7faac' sqlid='f601ur21vg07v'
begin
    for x in (select to_date(20,'J') x from dual) loop dbms_output.put_line( 'hello world ' || x.x ); end loop;
end;
END OF STMT
PARSE #3914836:c=0,e=163,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1320351618864610
=====================
PARSING IN CURSOR #3877348 len=34 dep=1 uid=374 oct=3 lid=374 tim=1320351618864806 hv=1142410920 ad='219d8eb0' sqlid='86gp9td21gmp8'
SELECT TO_DATE(20,'J') X FROM DUAL
END OF STMT
PARSE #3877348:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1388734953,tim=1320351618864805
EXEC #3877348:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1388734953,tim=1320351618864882
FETCH #3877348:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=1388734953,tim=1320351618865078
STAT #3877348 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
CLOSE #3877348:c=0,e=2,dep=1,type=3,tim=1320351618865249
EXEC #3914836:c=0,e=604,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1320351618865275
ERROR #2:err=1841 tim=1320351618865286


The failure is associated with the plsql block - not the sql statement - the sql statement was successful, it actually returned a row.

I'm not entirely sure how to classify this one. Looks like a mixture of

o sql bug
o plsql bug
o sqlplus bug

I think there are three bugs here.

whacky indeed

Sokrates, November 03, 2011 - 10:06 am UTC

sokrates@11.2.0.2 > alter session set nls_calendar='Arabic Hijrah';

Session altered.

sokrates@11.2.0.2 > select dump(to_date(20,'J')) from dual;

DUMP(TO_DATE(20,'J'))
--------------------------------------------------------------------------------
Typ=13 Len=8: 152,237,1,20,0,0,0,0

sokrates@11.2.0.2 > select sysdate-to_date(20,'J') from dual;

SYSDATE-TO_DATE(20,'J')
-----------------------
             2455849.67

sokrates@11.2.0.2 > select sysdate - ( sysdate-to_date(20,'J') ) "to_date(20, 'J')" from dual;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



no rows selected


hope you enjoyed your day in Copenhagen.
Don't miss the Smørrebrød !
Tom Kyte
November 03, 2011 - 10:14 am UTC

I will file a bug next week when I get back and let them sort it out ;)

IF the NLS_CALENDAR is not GREGORIAN

Sridhar, November 12, 2011 - 1:38 am UTC

Hi Tom,
Just wanted to let you know that we did find an elegant solution for this problem instead of switching the NLS_CALENDAR parameter:

alter session set nlS_calendar = 'arabic hijrah';
select to_char(to_date(1,'J'),'JSP','nls_calendar=''gregorian''') from dual;

But this works only for 11g environment, Fails in 10g environment(not sure why, if you could explain it would be of great help!! :) ). For 10g, the query has to be re-written as :

alter session set nlS_calendar = 'arabic hijrah';
select to_char(to_date(1,'J','nls_calendar=''gregorian'''),'JSP',
'nls_calendar=''gregorian''') from dual;

Cheers,
Sri
Tom Kyte
November 15, 2011 - 7:32 am UTC

NLS_CALENDAR is not supported there. I has that thought at first as well, but according to documentation only NLS_DATE_LANGUAGE is supported there.

http://docs.oracle.com/docs/cd/E11882_01/server.112/e26088/functions203.htm#SQLRF06132

The 'nlsparam' argument specifies the language of the text string that is being converted to a date. This argument can have this form:

'NLS_DATE_LANGUAGE = language'

Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.

Ratu Sitiveni, July 16, 2012 - 5:05 pm UTC

please can you put this in words $31,601.62
Tom Kyte
July 17, 2012 - 7:59 am UTC

you could not do this?? the hard part was done already..


ops$tkyte%ORA11GR2> select spell_number( trunc(x) ) || ' dollars and ' || spell_number( round( 100*(x-trunc(x)),0 ) ) || ' cents'
  2    from (select 31601.62 x from dual)
  3  /

SPELL_NUMBER(TRUNC(X))||'DOLLARSAND'||SPELL_NUMBER(ROUND(100*(X-TRUNC(X)),0))||
-------------------------------------------------------------------------------
Thirty-One thousand Six Hundred One dollars and Sixty-Two cents

ops$tkyte%ORA11GR2> 

Spell the numbers in a easy way

hearthacker, February 21, 2013 - 12:40 am UTC

create or replace function spell_number (num in number)
return varchar2
is
sn varchar(100) ;
begin
if
length(num)=0 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=1 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=2 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=3 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=4 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=5 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=6 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
elsif length(num)=7 then
sn:=initcap(to_char(to_date(num,'j'),'JSP'));
end if;
return sn;
end ;

select spell_number(:sn) "The Number Spell In Words" from dual;
any errors or suggestions let me know
Tom Kyte
February 25, 2013 - 10:13 am UTC

I want to spell 123456789

what now?

all of your bits of code are identical:

sn:=initcap(to_char(to_date(num,'j'),'JSP'));


what is the purpose of all of the if then elses???

Your entire procedure is equivalent to

to_char( to_date( num,'J'),'Jsp')


I don't understand what you are trying to do.

Spelling Numbers properly

Anthony, June 11, 2013 - 4:07 pm UTC

5,373,484 = five million three hundred seventy-three thousand four hundred eighty-four

You can check your answer here = http://www.howtospellnumbers.com/
Tom Kyte
June 18, 2013 - 2:48 pm UTC

we agree????


ops$tkyte%ORA11GR2> select spell_number( 5373484 ) from dual;

SPELL_NUMBER(5373484)
-------------------------------------------------------------------------------
Five million Three Hundred Seventy-Three thousand Four Hundred Eighty-Four



your point is?

Correct spelling

Anthony Montoya, July 25, 2013 - 8:48 pm UTC

This is how you spell 5,373,484

five million three hundred seventy-three thousand four hundred eighty-four

http://www.howtospellnumbers.com/
Tom Kyte
August 02, 2013 - 5:21 pm UTC

and still I ask

what is your point? how are they spelled differently?

ops$tkyte%ORA11GR2> select spell_number( 5373484 ) from dual;

SPELL_NUMBER(5373484)
-------------------------------------------------------------------------------
Five million Three Hundred Seventy-Three thousand Four Hundred Eighty-Four

five million three hundred seventy-three thousand four hundred eighty-four


??? If you've gotten wrapped around the axle that the case is wrong or something - then I'd

a) beg to differ. and pointing me to a web site named "how to do something" doesn't mean they know how to do something

b) would point you to lower, if you like the other way. a pretty trivial and obvious extension if you like.

A reader, January 02, 2014 - 5:58 pm UTC

Very usefull to some extent but this query not integrating to all the numbers

spell_indian_moeny is useful

Shrirama, July 16, 2014 - 6:44 am UTC

spell_indian_money is very very useful

how to Get amount in words using function with decimal

A.Hakim, February 24, 2017 - 6:12 pm UTC

how to Get amount in words using function with decimal
Connor McDonald
February 25, 2017 - 1:14 am UTC

Just scroll a few lines up from the bottom for this example

ops$tkyte%ORA11GR2> select spell_number( trunc(x) ) || ' dollars and ' || spell_number( round( 100*(x-trunc(x)),0 ) ) || ' cents'
  2    from (select 31601.62 x from dual)
  3  /

SPELL_NUMBER(TRUNC(X))||'DOLLARSAND'||SPELL_NUMBER(ROUND(100*(X-TRUNC(X)),0))||
-------------------------------------------------------------------------------
Thirty-One thousand Six Hundred One dollars and Sixty-Two cents

ops$tkyte%ORA11GR2> 


A reader, April 21, 2020 - 4:37 pm UTC

one hundred twenty-eight novemdecillion,
eight hundred thirty-seven octodecillion,
two hundred thirty-five septendecillion,
eight hundred twenty-seven sexdecillion,
three hundred eighty-two quindecillion,
seven hundred fifty-nine quattuordecillion,
eight hundred thirty-five tredecillion,
four hundred eighty-seven duodecillion,
two hundred eighty-nine undecillion,
three hundred twenty-eight decillion,
five hundred ninety-four nonillion,
fifty-seven octillion,
two hundred seventy-eight septillion,
seven hundred thirty-four sextillion,
seven hundred thirty-nine quintillion,
four hundred seventy-four quadrillion,
nine hundred fifty-four trillion,
five hundred seventy-four billion,
nine hundred eighty-five million,
seven hundred thirty-four thousand,
nine hundred fifty
Connor McDonald
April 22, 2020 - 4:01 am UTC

....is not my salary

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library