Show Me The Money

Money, money, money. It's a rich man's world. It can't buy you love, but as we are constantly reminded it can buy you cheap Levitra and pre-approved loans, and those are just as good and will probably boost this site's stats. It makes the world go round. It's great - but how to format it?

Well, here is one way. The editing history tells the whole story.

/*=============================================================================
Procedure:    FN_FORMAT_DOLLARS
Description:  Formats a number as dollars (2 decimals)

MODIFICATION HISTORY:
Person        Date        Comments
---------     ------      -------------------------------------------
XX            01/24/03    Created
SF            08/05/03    Rewrote the function as a TO_CHAR statement
==============================================================================*/

FUNCTION fn_format_dollars (p_dollars IN NUMBER)
    RETURN VARCHAR2
IS
    -- v_dollars   VARCHAR2 (50);
BEGIN

    /*
    v_dollars := TO_CHAR (p_dollars);
    IF INSTR (v_dollars, '.') = 0
    THEN
        v_dollars :=  v_dollars  || '.';
    END IF;

    -- too many decimal places...
    WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') > 2
    LOOP
        v_dollars := SUBSTR (v_dollars, 1, LENGTH (v_dollars) - 1);
    END LOOP;

    -- not enough decimal places...
    WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') < 2
    LOOP
        v_dollars := v_dollars || '0';
     END LOOP;
    */

    RETURN TO_CHAR(p_dollars, 'FM999999999990.00');

END fn_format_dollars;

Many thanks to rd for sending this in.