Salim berbagi…. tempat belajar dan berinteraksi….


Function untuk Terbilang Nominal (in English)
March 12, 2010, 7:58 AM
Filed under: ORACLE

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 trunc( p_number );
       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_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;
 
       return l_return;
   end;
   /


Leave a Comment so far
Leave a comment



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



%d bloggers like this: