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.
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
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
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;
/
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
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...
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
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.
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.
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
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;
/
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.
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
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
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
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
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
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
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!)
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!
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
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?
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
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
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
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
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,
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 ' );
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
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
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
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
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
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
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.
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
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
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'"
)
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 !
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
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
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
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
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
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
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
April 22, 2020 - 4:01 am UTC
....is not my salary