1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to convert whole numbers and decimal numbers into words in oracle.,?!

Discussion in 'SQL PL/SQL' started by Vicky, Aug 1, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Could U tel me, how to convert whole numbers and decimal numbers into words and vice-versa in oracle.,?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    The standard decision isn't present.
    It is necessary to write the functionality or to find a ready framework

    For example :
    SELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') FROM DUAL;
    Note :Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.


    Code (SQL):

    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;
    /

    SELECT spell_number (53734555555585) FROM DUAL;


     
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    What V can do for decimal values.,?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    You can explain, what conversions you need to make?
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    I've used Sergey's script (thanks Sergey !) and adapted it to handle the decimal part by using a recursive call.

    Try it:

    Code (SQL):
    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_str_dec myArray
             := myArray ('',
                         'ten-',
                         'hundred-');
     
       l_num      VARCHAR2 (50) DEFAULT TRUNC (p_number);
       l_return   VARCHAR2 (4000);
       l_decimal  NUMBER DEFAULT (p_number - l_num);
       l_len_decimal NUMBER DEFAULT LENGTH(l_decimal)-1;
    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)
                || CASE WHEN i!=1 THEN ' ' ELSE '' END
                || l_return;
          END IF;
     
          l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
       END LOOP;
       --
       IF l_decimal !=0
       THEN
          l_return := l_return
                       || 'and '
                       || spell_number((l_decimal*POWER(10, l_len_decimal)))
                       || CASE
                            WHEN (l_len_decimal < 3) THEN l_str_dec(l_len_decimal)
                            ELSE l_str_dec(MOD(l_len_decimal,3)+1) || l_str(TRUNC(l_len_decimal/3)+1)
                          END  
                       || 'ths';
       END IF;
       --
       RETURN l_return;
    END;
    /

    SELECT spell_number(345671234.7865) FROM dual;

    SPELL_NUMBER(345671234.7865)
    ----------------------------------------------------------------------------------------------------------------------------------
    Three Hundred Forty-Five million Six Hundred Seventy-One thousand Two Hundred Thirty-Four AND Seven thousand Eight Hundred Sixty-Five ten-thousandths
     
     
    Vicky likes this.