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!

SQL to convert number to words

Discussion in 'SQL PL/SQL' started by lovelandj, Mar 27, 2009.

  1. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    how can we convert number to words? i heard there is something called Julian day conversion but it is not working for me.

    thanks
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    not working? what did you try?

    This is the oldest trick in the book :p

    Code (Text):

    SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words FROM   dual;
     
    Cheers!
     
  3. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    hey thanks it works but i want to convert decimals too. I mean
    Code (Text):
    SELECT TO_CHAR(TO_DATE(123.50,'J'),'JSP') to_words FROM   dual;
    throws an error
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hmmm how about this...
    Code (Text):

    SELECT    TO_CHAR (TO_DATE (TRUNC (&num), 'J'), 'JSP')
           || ' Point '
           || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (&num, INSTR (&num, '.') + 1)),'J'),'JSP')
      FROM DUAL;
     
  5. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    hey thanks boss it's working!!! silly me i should have figured that out myself!
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  7. Deepakraj

    Deepakraj Guest

    Hi Guys try out dis code ,....



    CREATE OR REPLACE FUNCTION TOWORDS(NUMBER1 IN NUMBER,
    CURRENCY1 IN VARCHAR2) RETURN VARCHAR2

    IS
    INWORDS VARCHAR2(1000);
    CURRENCY2 VARCHAR2(1);
    CURTYPE VARCHAR2(6);
    CURTYPE1 VARCHAR2(8);
    MMAX NUMBER := 5373484;
    NUMBER2 NUMBER(20, 2) := 0;
    BEGIN
    NUMBER2 := NUMBER1;
    CURRENCY2 := CURRENCY1;
    ---- ---- Defining and setting up currency types ---- ---
    /*"E" FOR EURO, "I" FOR PUNT AND "S" FOR STERLING */
    IF UPPER(CURRENCY2) IN ('I', 'S') THEN
    CURTYPE := ' PENCE';
    CURTYPE1 := ' POUNDS';
    ELSIF UPPER(CURRENCY2) = 'E' THEN
    CURTYPE := ' PENCE';
    CURTYPE1 := ' EUROS';
    ELSIF UPPER(CURRENCY2) = 'D' THEN
    CURTYPE := ' CENT';
    CURTYPE1 := ' DOLLARS';
    ELSIF UPPER(CURRENCY2) = 'S' THEN
    CURTYPE := ' PENCE';
    CURTYPE1 := ' POUNDS';
    ELSIF UPPER(CURRENCY2) = 'N' THEN
    CURTYPE := ' KOBO';
    CURTYPE1 := ' NAIRA';
    ELSE
    CURTYPE := ' PENCE';
    CURTYPE1 := ' POUNDS';
    END IF;

    --- --- Process Starts here --- ---
    IF NUMBER2 = 0 THEN
    RETURN('ZERO' || CURTYPE1);
    ELSIF NUMBER2 < 0 THEN
    RETURN('ERROR - NEGATIVE AMOUNT');
    ELSE
    SELECT DECODE(SIGN(LENGTH(TO_CHAR(TRUNC(NUMBER2))) - 7),
    -1,
    LTRIM(RTRIM(DECODE(SIGN(1 - FLOOR(NUMBER2)),
    1,
    '' ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    0,
    ' ',
    TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    TO_CHAR(TO_DATE(TRUNC(NUMBER2), 'J'),
    'JSP') || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' ||
    TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE)))),
    DECODE(LENGTH(TO_CHAR(TRUNC(NUMBER2))),
    7,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    1)),
    'J'),
    'JSP') || ' MILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 2, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    2,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    8,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    2)),
    'J'),
    'JSP') || ' MILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 3, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    3,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    9,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    3)),
    'J'),
    'JSP') || ' MILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 4, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    4,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    10,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    1)),
    'J'),
    'JSP') || ' BILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 2, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    2,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 5, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    5,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    11,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    2)),
    'J'),
    'JSP') || ' BILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 3, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    3,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 6, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    6,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    12,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    3)),
    'J'),
    'JSP') || ' BILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 4, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    4,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 7, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    7,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    13,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    1)),
    'J'),
    'JSP') || ' TRILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 2, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    2,
    3)),
    'J'),
    'JSP') || ' BILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 5, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    5,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 8, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    8,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    14,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    2)),
    'J'),
    'JSP') || ' TRILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 3, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    3,
    3)),
    'J'),
    'JSP') || ' BILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 6, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    6,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 9, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    9,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE),
    15,
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    1,
    3)),
    'J'),
    'JSP') || ' TRILLION ' ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 4, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    4,
    3)),
    'J'),
    'JSP') || ' BILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 7, 3)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    7,
    3)),
    'J'),
    'JSP') || ' MILLION ') ||
    DECODE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2), 10, 6)),
    0,
    '',
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(NUMBER2),
    10,
    6)),
    'J'),
    'JSP')) || CURTYPE1 ||
    DECODE(SIGN(TO_NUMBER(NUMBER2 - FLOOR(NUMBER2))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(NUMBER2 -
    FLOOR(NUMBER2))),
    'J'),
    'JSP') || CURTYPE)))
    INTO INWORDS
    FROM DUAL;
    RETURN(INWORDS);
    END IF;
    END;
    /
     
  8. Deepakraj

    Deepakraj Guest

    dai chk my code da pachella pudingi
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "Hi Guys try out dis code ,...."

    'dis code'?? Please, this is a forum for professionals and others to discuss Oracle and ask/answer questions and, as such, posts should use proper English. Yes, it's an impressive piece of code, confusing to read because it's poorly formatted thus it's difficult to see WHY you wrote so much to do so little. Comments would have helped tremendously in explaining the reasoning for this attempt. It works, AFTER digging through the code to find valid values for CURRENCY1, which are NOT clearly listed in comments in the code OR in your post providing the function. I tried it because I could not understand why you chose this over a simple 'select to_char(to_date(NUMBER1,'J'),'JSP') from dual'; I realized after attempting to submit an 8 digit number to to_date for the Julian format that it won't process any number greater than 9999999 yet your 'mess' will.

    Please clean up your code, format it so it's readable and add comments describing what you're doing and why, then post the modified code so we can all benefit from your work.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think this works as well as yours and is much shorter and easier to read:

    Code (SQL):
    CREATE OR REPLACE
    FUNCTION spell_amount( p_number IN NUMBER, p_curr IN varchar2 )
    RETURN varchar2
    --
    -- Expected values for p_curr are 'E',  'I',   'D',   'S',        'N'
    --                              (euro, punt, dollars, sterling,  naira)
    -- Any other character provided will return 'Pounds' as the
    -- monetary unit
    --
    AS
        --
        -- Build an array to translate the amount
        -- to the correct specifier
        --
        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;
            --
            -- 'Walk' the number submitted, parsing
            -- the number into values less than 1000
            -- until there is no more of the number to parse
            --
            -- This gets past the 7-digit limit of the 'J' date
            -- format specifier and allows us to process very large
            -- numbers
            --
            IF ( substr(l_num, LENGTH(l_num)-2, 3) <> 0 )
            THEN
            --
            -- Concatenate the translated values to the output
            -- string, using the array defined at the top to
            -- provide the correct specifier
            --
               l_return := to_char(
                               to_date(
                                substr(l_num, LENGTH(l_num)-2, 3),
                                  'J' ),
                           'Jsp' ) || l_str(i) || l_return;
            END IF;
            --
            -- 'Remove' the digits we just processed and continue on
     --
            l_num := substr( l_num, 1, LENGTH(l_num)-3 );
        END loop;
        --
        -- Add the currency specifier based upon the submitted value
        --
        IF p_curr IN ('I','S') THEN
     l_return := l_return || ' Pounds';
        elsif p_curr = 'D' THEN
     l_return := l_return || ' Dollars';
        elsif p_curr = 'N' THEN
     l_return := l_return || ' Naira';
        ELSE
     l_return := l_return || ' Pounds';
        END IF;
        --
        -- Return the final string including currency
        --
        RETURN l_return;
    END;
    /
     
    The code above produces the following result:

    Code (SQL):
    SQL> SELECT spell_amount(12345678, 'D') amount FROM dual;
    AMOUNT
    ----------------------------------------------------------------------------------------
    Twelve million Three Hundred Forty-Five thousand Six Hundred Seventy-Eight Dollars
    SQL>
    SQL> SELECT spell_amount(12345678, 'I') amount FROM dual;
    AMOUNT
    ----------------------------------------------------------------------------------
    Twelve million Three Hundred Forty-Five thousand Six Hundred Seventy-Eight Pounds
    SQL>
    And it's much shorter.
     
    gurujothi likes this.
  11. Deepakraj

    Deepakraj Guest

    will your code works out with decimal values...????
     
  12. Deepakraj

    Deepakraj Guest

    Mr.David ,
    Ur code wont work with decimal values
     
  13. Deepakraj

    Deepakraj Guest

    Hi David .
    I have made some changes in your code, Now it'll work with the decimal values, and thanks for you advice in the code formats , its very usefull for me, and since i am new to this blog i apologise for improper communication

    Thanks & Regards...
    Deepakraj
    -----------------------------------------------
    CREATE OR REPLACE
    FUNCTION spell_amount( p_number IN NUMBER, p_curr IN varchar2 )
    RETURN varchar2
    --
    -- Expected values for p_curr are 'E', 'I', 'D', 'S', 'N'
    -- (euro, punt, dollars, sterling, naira)
    -- Any other character provided will return 'Pounds' as the
    -- monetary unit
    --
    AS
    --
    -- Build an array to translate the amount
    -- to the correct specifier
    --
    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);
    l_point VARCHAR2(1000);
    l_curr VARCHAR2(10) := upper(p_curr);
    BEGIN
    FOR i IN 1 .. l_str.COUNT
    loop
    exit WHEN l_num IS NULL;
    --
    -- 'Walk' the number submitted, parsing
    -- the number into values less than 1000
    -- until there is no more of the number to parse
    --
    -- This gets past the 7-digit limit of the 'J' date
    -- format specifier and allows us to process very large
    -- numbers
    --
    IF ( substr(l_num, LENGTH(l_num)-2, 3) <> 0 )
    THEN
    --
    -- Concatenate the translated values to the output
    -- string, using the array defined at the top to
    -- provide the correct specifier
    --
    l_return := to_char(
    to_date(
    substr(l_num, LENGTH(l_num)-2, 3),
    'J' ),
    'Jsp' ) || l_str(i) || l_return;
    END IF;
    --
    -- 'Remove' the digits we just processed and continue on
    --
    l_num := substr( l_num, 1, LENGTH(l_num)-3 );
    END loop;
    --
    -- Add the currency specifier based upon the submitted value
    --
    -- Added this query to get the decimal values from the amount--


    SELECT DECODE(SIGN(TO_NUMBER(p_number - FLOOR(p_number))),
    0,
    ' ',
    ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *
    TO_NUMBER(p_number -
    FLOOR(p_number))),
    'J'),
    'JSP'))
    INTO l_point
    FROM dual;

    IF l_curr IN ('I','S') THEN
    l_return := l_return || ' Pounds '|| l_point ||' Pence';
    elsif l_curr = 'D' THEN
    l_return := l_return || ' Dollars '|| l_point||' Cent';
    elsif l_curr = 'N' THEN
    l_return := l_return || ' Naira '|| l_point||' Kobo';
    ELSE
    l_return := l_return || ' Pounds '|| l_point||' Pence';
    END IF;
    --
    -- Return the final string including currency
    --
    RETURN l_return;
    END;
     
    Sadik likes this.
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And so it doesn't as I wrote it; your modification is far too much when you already have the non-decimal portion of the number spelled for you. This is less work:

    Code (SQL):
    CREATE OR REPLACE
    FUNCTION spell_amount( p_number IN NUMBER, p_curr IN varchar2 )
    RETURN varchar2
    --
    -- Expected values for p_curr are 'E', 'I', 'D', 'S', 'N'
    -- (euro, punt, dollars, sterling, naira)
    -- Any other character provided will return 'Pounds' as the
    -- monetary unit
    --
    AS
    --
    -- Build an array to translate the amount
    -- to the correct specifier
    --
    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);
    l_point VARCHAR2(1000);
    l_curr VARCHAR2(10) := UPPER(p_curr);
    BEGIN
    FOR i IN 1 .. l_str.COUNT
    loop
    exit WHEN l_num IS NULL;
    --
    -- 'Walk' the number submitted, parsing
    -- the number into values less than 1000
    -- until there is no more of the number to parse
    --
    -- This gets past the 7-digit limit of the 'J' date
    -- format specifier and allows us to process very large
    -- numbers
    --
    IF ( substr(l_num, LENGTH(l_num)-2, 3) <> 0 )
    THEN
    --
    -- Concatenate the translated values to the output
    -- string, using the array defined at the top to
    -- provide the correct specifier
    --
    l_return := to_char(
    to_date(
    substr(l_num, LENGTH(l_num)-2, 3),
    'J' ),
    'Jsp' ) || l_str(i) || l_return;
    END IF;
    --
    -- 'Remove' the digits we just processed and continue on
    --
    l_num := substr( l_num, 1, LENGTH(l_num)-3 );
    END loop;
    --
    -- Add the currency specifier based upon the submitted value
    --
    -- Added this query to get the decimal values from the amount--
     
    l_point := ' AND ' || TO_CHAR(TO_DATE(TRUNC(100 *TO_NUMBER(p_number -FLOOR(p_number))),'J'),'jsp');
    IF l_curr IN ('I','S') THEN
    l_return := l_return || ' Pounds '|| l_point ||' Pence';
    elsif l_curr = 'D' THEN
    l_return := l_return || ' Dollars '|| l_point||' Cent';
    elsif l_curr = 'N' THEN
    l_return := l_return || ' Naira '|| l_point||' Kobo';
    ELSE
    l_return := l_return || ' Pounds '|| l_point||' Pence';
    END IF;
    --
    -- Return the final string including currency
    --
    RETURN l_return;
    END;
     
     
  15. Deepakraj

    Deepakraj Guest

    What will happen if we enter the decimal values ...? your code spits you... without throwing an exception, and the output what you get is wrong.. a professional should think of all the possibilities of the input and the output... so Mr. David dont be a junk coder, be a professional...,
     
  16. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Deepak, there is no need for such language. David is one of the best programmers in Oracle I have known and takes time out daily to help other people and newbies to learn oracle and solve problems.
     
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You obviously did not read my last post editing your changes to make them

    1) more readable
    2) independent of a select statement

    The result obtained from my modifications provides the correct output; please pay more attention to what has been posted before you run off and make such comments.
     
    Nizam likes this.
  18. Nizam

    Nizam Guest

    Thanks David. In fact I was searching for a function to convert currency to words. So thanks again and if you have time can you please advise me the following;

    I am a new to Oracle just did my sql certification and want to develop/see myself as a developer. Therefore I would like you to please suggest me which database and developer suite I must download and which books I should read.

    I am very much passionate about oracle programming and want to work hard to be a good developer.

    Hope to receive your valuable suggestion....

    By the way thanks again....[/COLOR]