Number to word function

[joyal]

Can anyone help me? Is there any function or packaged procedure that can convert the number to words.

Thanks

Joyal

[rohitsn]

Hi,
There is no function or package pocedure that can convert the number to words.U have to draft ur own procedure or function to do it.

Regards,
Rohit Nirkhe,oracle DBA,OCP 8i
[email protected]

[jmodic]

There is an old trick for converting numbers to english words by using special format of Julian dates. Example:

SQL> SELECT TO_CHAR( TO_DATE( 1234567, 'J'),' JSP') FROM dual;

TO_CHAR(TO_DATE(1234567,'J'),'JSP')
---------------------------------------------------------------------
ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN

SQL>

You are limited to up-to 7 digits ******* values in the range that Julian dates can handle, but with some inventive combining of functions you could even surpass those limits. BTW, format picture 'J' stands for Julian date conversion (date to number), while 'JSP' stands for Julian dates SPelled.

HTH,

[joyal]

Thanks a mio

[sudip]

Here, I got this from someone,


create or replace
function spell_number( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
check_number number;
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);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
SELECT to_number(substr(l_num, length(l_num)-2, 3))
INTO check_number
FROM dual;
IF check_number > 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 );
dbms_output.put_line(‘return value’||’ ‘||l_return);
dbms_output.put_line(‘num value’||’ ‘||l_num);
end loop;
return l_return;
end;
/