Formatting a field to have 2 decimal places

[dkwong]

Is there a built-in function in PL/SQL to do the following or should I just write a function:

I want to format a dollar amount field. If the field value has no decimal places, format the number to have two decimal places. If it has 1 decimal place, add a zero after it to have two decimal places. Leave the field alone if there are already two decimal places.

 

[jee]

Assuming your currency value is in a column of type number and you want to return a charcter use the to_char function with a format like '$9999.99':

to_char (dollar_col, '$9999.99')

This will append a '$' to the front.

 

[dkwong]

Thanks, jee!

 

[rcurva]

Alternatively, you can also use the NLS_% setup.

to_char (dollar_col, 'L9999.99')

The currency here willl depend on your NLS_CURRENCY or NLS_LANGUAGE that is setup for your db.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War