1. Registered User
Join Date
Dec 2003
Posts
1,074

Is there any built in formatting function that could convert

2,123

to

"two thousand one hundred and twenty three"

-cf

2. Registered User
Join Date
May 2006
Posts
132
Sure...check here Spelling Out a Number.

3. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
My own version of a spell function that handles cents and MUCH bigger numbers follows.

Code:
```function spell_number( p_number in number,
p_format in varchar2)
return varchar2
is
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 trunc( p_number );
l_return varchar2(4000);
x_point varchar2(20);
begin
if upper(p_format) = 'D' then
x_point := ' Dollars and';
else
x_point := ' point';
end if;
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
-- beginning of section added to include decimal places:
if to_char( p_number ) like '%.%'
then
l_num := substr( p_number, instr( p_number, '.' )+1 );
if p_format = 'D' then
if l_num > 99 then
return null;
elsif length(l_num) < 2 then
l_num := l_num||'0';
end if;
l_return := l_return||' Dollars and '||l_num||' Cents';
return l_return;
end if;
if l_num > 0
then
l_return := l_return || x_point;
for i in 1 .. length (l_num)
loop
exit when l_num is null;
if substr( l_num, 1, 1 ) = '0'
then
l_return := l_return || ' zero';
else
l_return := l_return
|| ' '
|| to_char(
to_date(
substr( l_num, 1, 1),
'j' ),
'jsp' );
end if;
l_num := substr( l_num, 2 );
end loop;
end if;
else
if p_format = 'D' then
l_return := l_return||' Dollars and 00 Cents';
end if;
end if;
-- end of section added to include decimal places
return l_return;
end spell_number;```

4. Registered User
Join Date
Apr 2004
Posts
246
fyi, the values in the array after "duodecillion" are:

' tredecillion ',
' quattuordecillion ',
' quindecillion ',
' sexdecillion ',
' septendecillion ',
' octodecillion ',
' novemdecillion ',
' vigintillion '

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•