Home » SQL & PL/SQL » SQL & PL/SQL » numeric value converted to wordings (Oracle 10G)
numeric value converted to wordings [message #303751] Sun, 02 March 2008 12:09 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

I have cheque amount as Rs.37,345.76

This has to be worded as Rupees Thirty seven thousand
three hundred forty five and paise seventy six only.

This has been solved through creating table
where
num_val num_word
1 one
2 two
..
..
78 seventy eight

Using substr, searching and concatinating words.

I was told there is separate function available for converting
numeric into words

pl help me..

yours
dr.s.raghunathan

Re: numeric value converted to wordings [message #303760 is a reply to message #303751] Sun, 02 March 2008 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
* Use SP format model.
* Build your own function.
* Search on AskTom this has been done in several languages.

Regards
Michel
Re: numeric value converted to wordings [message #303763 is a reply to message #303751] Sun, 02 March 2008 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
-- function:
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 ', 
                                 ' Neel ',
                                 ' Padam ',
                                 ' Shankh ',
                                 ' Maha-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 := rpad (substr (round (p_number, 2), 
                               instr (p_number, '.' ) + 1), 
                       2, '0');
        if l_num > 0
        then
            l_return := l_return || ' and paise '
            || to_char(
                   to_date(
                    l_num,
                     'J' ),
               'Jsp' );
        end if;
    end if;

    return 'Rupees ' || l_return || ' only';
end spell_indian_money;
/




-- usage example:
SCOTT@orcl_11g> select spell_indian_money (37345.76) from dual
  2  /

SPELL_INDIAN_MONEY(37345.76)
--------------------------------------------------------------------------------
Rupees Thirty-Seven Thousand Three Hundred Forty-Five and paise Seventy-Six only

SCOTT@orcl_11g>


Re: numeric value converted to wordings [message #303835 is a reply to message #303763] Mon, 03 March 2008 00:13 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Thanks a lot
Yours
dr.s.raghunathan
Previous Topic: How to use a schema name parameter in a procedure
Next Topic: Find 3 most current dates
Goto Forum:
  


Current Time: Sun Dec 04 17:01:07 CST 2016

Total time taken to generate the page: 0.04673 seconds