March 16, 2001 - 4pm Central time zone
Reviewer: A reader
April 30, 2001 - 3am Central time zone
Reviewer: Helena Markova from Bratislava, Slovakia
Problem with spell_number function
April 30, 2001 - 5am Central time zone
Reviewer: Nusrat from Mumbai, India
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
January 22, 2002 - 5pm Central time zone
Reviewer: A reader
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.
Followup January 22, 2002 - 6pm Central time zone:
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
January 22, 2002 - 7pm Central time zone
Reviewer: Jim from Australia
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
January 23, 2002 - 1am Central time zone
Reviewer: A reader
do you now any facility so i can here a sound for the output
Followup January 23, 2002 - 7am Central time zone:
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
January 23, 2002 - 3am Central time zone
Reviewer: Mohammed Al-moayed from Yemen
well, good I like these SQl , which add to my Skills new information , but Colud you please
Explain to me what
JSP Stand for
Followup January 23, 2002 - 7am Central time zone:
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
January 23, 2002 - 5am Central time zone
Reviewer: Claudio from Argentina
January 23, 2002 - 6pm Central time zone
Reviewer: Asif Merchant from NY
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;
/
Followup January 23, 2002 - 9pm Central time zone:
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
January 23, 2002 - 10pm Central time zone
Reviewer: A reader
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
Followup January 25, 2002 - 6am Central time zone:
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!
January 23, 2002 - 11pm Central time zone
Reviewer: Sazzadur Rahman Tusar from Bangladesh
select to_char( to_date(5373484,'J'),'Jsp') from dual is a very intelligent query to spell out a
number.
The above posting...
January 24, 2002 - 12pm Central time zone
Reviewer: A reader
Compact it is, efficient it is, clever it is, cryptic it most certainly is. Intelligent?.
Considering the alternatives, I'd certainly use it though...
Followup January 25, 2002 - 8am Central time zone:
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....
January 24, 2002 - 4pm Central time zone
Reviewer: A reader
Great !!!!
Does this JSP works for decimals also
ex 23456.66
Followup January 25, 2002 - 8am Central time zone:
it works for Julian dates -- you can use it as PART of your solution as I did.
Follow up - Intelligent Query & Decimals
January 24, 2002 - 5pm Central time zone
Reviewer: Jim from Australia
"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
January 24, 2002 - 6pm Central time zone
Reviewer: Barbara Boehmer from Norwalk, CA USA
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?
June 10, 2002 - 10pm Central time zone
Reviewer: Hing-chau Leung from Hongkong
Your solution is excellent. I just wonder is there any similar solution for other language.
Followup June 11, 2002 - 10am Central time zone:
No, it only works for english, sorry.
Nice ... but what about UK English?
June 11, 2002 - 11am Central time zone
Reviewer: Charlie from UK
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 <b>and</b> sixty-six thousand, four hundred <b>and</b> eleven.
Is there any way to do this, or do we have to suffer the rather grating Americanism ;-)
Thank you,
Charlie E.
Followup June 11, 2002 - 2pm Central time zone:
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
June 12, 2002 - 3am Central time zone
Reviewer: Sagi from India.
Once again thank you tom.
Regards,
Re: Nice ... but what about UK English?
June 12, 2002 - 4am Central time zone
Reviewer: Charlie E from England (where we invented English)
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
Followup June 12, 2002 - 7am Central time zone:
trivial change:
c/'Hundred', 'Hundred AND'/'Hundred ', 'Hundred AND '/
just add a space
other languages
June 13, 2002 - 12am Central time zone
Reviewer: Barbara Boehmer from Norwalk, CA USA
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;
/
Followup June 13, 2002 - 8am Central time zone:
Very cool -- thanks. Cannot vouch for the actual output -- the concept is sound
The last word on 'spell the number' (hopefully)
June 14, 2002 - 6am Central time zone
Reviewer: Ed Sleath from Cheshire, UK
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
June 14, 2002 - 10pm Central time zone
Reviewer: Barbara Boehmer from Norwalk, CA USA
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!!
September 17, 2002 - 5am Central time zone
Reviewer: yendra from Jakarta, Indonesia
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.
Followup September 17, 2002 - 8am Central time zone:
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
October 28, 2002 - 3am Central time zone
Reviewer: Kamran Laeeq from Pakistan
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
Followup October 28, 2002 - 7am Central time zone:
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
April 8, 2003 - 12pm Central time zone
Reviewer: ma from bristol, uk
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
April 8, 2003 - 12pm Central time zone
Reviewer: ma from bristol, uk
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
May 8, 2003 - 1pm Central time zone
Reviewer: Walker from Maryland, USA
Terrific Solution.
Thanks to everyone for their reviews.
spellnum for german language
August 14, 2003 - 8am Central time zone
Reviewer: Maik Rabe from Stuttgart, Germany
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
August 25, 2003 - 6am Central time zone
Reviewer: A reader
No review. Just thanks. well done everyone.
James
January 28, 2004 - 4am Central time zone
Reviewer: Helen from South Africa
Can you update the other page?
April 25, 2004 - 8pm Central time zone
Reviewer: Tom Best from PA
Tom - would you be so kind as to update this:
http://asktom.oracle.com/~tkyte/Misc/SpellANumber.html
to reflect this solution?
Thanks.
Followup April 26, 2004 - 6am Central time zone:
there, just made that page, this page :)
number 0 and numbers between 1721058 and 1721423
May 19, 2004 - 10am Central time zone
Reviewer: Laurent from Switzerland
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
Followup May 19, 2004 - 11am Central time zone:
very cool, we'll have to adjust for that.
spell_the_number function works for numbers between 1721058 and 1721423
May 22, 2004 - 4pm Central time zone
Reviewer: Barbara Boehmer from Riverside County, CA USA
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
June 17, 2004 - 9am Central time zone
Reviewer: Laurent Schneider from Switzerland
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
July 30, 2004 - 7am Central time zone
Reviewer: Ashwin from India
Just awesome guys.Thanks a lot.This helped me a lot.
converting cents into fraction
August 19, 2004 - 10pm Central time zone
Reviewer: Yugi
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
Followup August 19, 2004 - 10pm Central time zone:
& 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
August 19, 2004 - 11pm Central time zone
Reviewer: Yugi
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
Followup August 20, 2004 - 10am Central time zone:
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?
August 20, 2004 - 4am Central time zone
Reviewer: A reader
hi,
closing thread i got it.....
thanks
hi
August 20, 2004 - 5am Central time zone
Reviewer: A reader
hi all,
my mistake..i still have problem..pls post the whole script
thanks.
hi
August 22, 2004 - 8pm Central time zone
Reviewer: YUgi
sorry for the confusion....i got it...thanks for the time
Can reverse way is possible ? ( words to number )
August 26, 2004 - 2am Central time zone
Reviewer: pjp
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
Followup August 26, 2004 - 9am Central time zone:
believe you will be writing code for that yourself. there is no tricky format I know of to turn
words into numbers.
math
September 15, 2004 - 7pm Central time zone
Reviewer: ho from usa
thirty two and sixty seven thousand ninety four hundred thousanaths
math
September 15, 2004 - 7pm Central time zone
Reviewer: ho from usa
thirty two and sixty seven thousand ninety four hundred thousanaths
RE: Can reverse way is possible ? ( words to number )
November 4, 2004 - 8am Central time zone
Reviewer: Laurent Schneider from Switzerland
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
November 16, 2004 - 8pm Central time zone
Reviewer: doug from calgary, AB, CA
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
November 25, 2004 - 8pm Central time zone
Reviewer: maryah from FLORIDA
Not useful at all!
Because-too complicated
-hard to understand with all the ( : ; ) = -
-no information
-web page not designed well (eg. no color!)
Followup November 25, 2004 - 8pm Central time zone:
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....
November 26, 2004 - 3am Central time zone
Reviewer: Gj from UK
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!
Followup November 26, 2004 - 9am Central time zone:
yeah, the comment about "no colors" really made me laugh. The person sort of totally "misses the
point"
Hey there!
January 31, 2005 - 6pm Central time zone
Reviewer: Victoria Belmont from USA
Hey there this was a very useful site! thanx sooooooooooo much!
er5yujh5rl
February 8, 2005 - 11pm Central time zone
Reviewer: w5uwr6
5yeyttyrtuy etuw46ue6u et6yuie56i
Followup February 9, 2005 - 2am Central time zone:
^@$@$GRQtrqgfa
I feel like I'm commicating now....
Thanks...
March 21, 2005 - 4am Central time zone
Reviewer: Kiran Shah from India
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
April 11, 2005 - 2am Central time zone
Reviewer: Nikunj Thaker from Ahmedabad,India
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
April 25, 2005 - 10am Central time zone
Reviewer: Andrea Foucher from Michigan
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?
Followup April 25, 2005 - 1pm Central time zone:
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 )
April 26, 2005 - 9am Central time zone
Reviewer: Laurent Schneider from Switzerland
> 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
Followup April 26, 2005 - 9am Central time zone:
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 )
April 27, 2005 - 5am Central time zone
Reviewer: Laurent Schneider from Switzerland
great piece of code :-)
correction of Indian version
April 30, 2005 - 2pm Central time zone
Reviewer: Barbara Boehmer from Riverside County, CA USA
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
June 3, 2005 - 4pm Central time zone
Reviewer: Hakim.Zen from USA
Too much code, it can be done with 1/2 as much
my solution is superior
Followup June 3, 2005 - 5pm Central time zone:
might be superior, but it sure is "missing"
Check this out
June 29, 2005 - 3am Central time zone
Reviewer: Yasser Qureshi from Pakistan
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
July 8, 2005 - 4pm Central time zone
Reviewer: Hassan from london
how do u spell 40
Followup July 8, 2005 - 6pm Central time zone:
You would have to find "U" and ask "U" how "U" spells forty
RE: check this out
July 22, 2005 - 9am Central time zone
Reviewer: A reader
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
August 28, 2005 - 8am Central time zone
Reviewer: Jenaya from Australia
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
Followup August 28, 2005 - 8am Central time zone:
not sure what you mean? You have written the large numbers already.
hahahahahaha............
September 1, 2005 - 10am Central time zone
Reviewer: Jay from Milwaukee, WI USA
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
October 3, 2005 - 3am Central time zone
Reviewer: 90 from 89
90-9
Number to Words
October 26, 2005 - 4pm Central time zone
Reviewer: Rajni Kant Tahlyan from Galesburg, MI USA
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
November 11, 2005 - 5am Central time zone
Reviewer: Asm Naveed from Pakistan
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,
Followup November 12, 2005 - 8am Central time zone:
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
December 27, 2005 - 7pm Central time zone
Reviewer: YM Leong from Singapore
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 ' );
Followup December 28, 2005 - 9am Central time zone:
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
February 6, 2006 - 3am Central time zone
Reviewer: matty from australia
how do you do this in numbers sixty-four thousand, nine hundred and twenty-eight
Followup February 7, 2006 - 12am Central time zone:
not sure what you mean? are you just asking for:
64,928
?
write the number
February 6, 2006 - 3am Central time zone
Reviewer: matty from australia
how do you do this in numbers sixty-four thousand, nine hundred and twenty-eight
Words to numbers?
February 7, 2006 - 5am Central time zone
Reviewer: Neil from London
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...
August 28, 2006 - 11pm Central time zone
Reviewer: Victoria Curry from Shaw, USA
USE DIGITS TO WRITE THIS NUMBER: TEN BILLION, TWO HUNDRED FIVE MILLION , FORTY-ONE
Followup August 29, 2006 - 7am Central time zone:
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
October 13, 2006 - 8am Central time zone
Reviewer: senthil
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
Followup October 13, 2006 - 2pm Central time zone:
won't happen, doesn't work that way.
You use the COMMENT sql command to comment on tables and columns.
Indian Currentcy in words
February 15, 2007 - 5am Central time zone
Reviewer: Manjunath from India
Excellent job..hats off all of u..
this code is awesome..Thank u
Number Spelling in Portuguese
August 23, 2007 - 10am Central time zone
Reviewer: Nuno Farinha from Lisbon, Portugal
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','dez
oito','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
February 13, 2008 - 3am Central time zone
Reviewer: Sharon from UAE
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
November 6, 2009 - 4am Central time zone
Reviewer: A reader
|