Tuesday, August 08, 2006

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.

4 comments:

Dustin said...

It would be nice if you showed how clever you are with an example of the right way to do it as well.

This would help folks who aren't as familiar with Oracle as you, but know that there must be a system function to handle it and come across your page with google.

Scott Swank said...

Dustin,

The original post shows the right way to do it -- just ignore the bit in the comments.

[code]
FUNCTION fn_format_dollars (p_dollars IN NUMBER)
RETURN VARCHAR2
IS
-- v_dollars VARCHAR2 (50);
BEGIN
RETURN TO_CHAR(p_dollars, 'FM999999999990.00');
END fn_format_dollars;
[/code]

Dustin said...

Thank you very much, and upon re-reading my post I realize it sounds snarky. I appreciate that you politely ignored it. :)

Sorry for not seeing that originally.

RICHGUY said...

I really like your blog- have to check on it more often. If you are interested in
web-businesses
maybe you should have a look at my blog :)