Oracle 10g Float format

Question

How can I return from float/decimal value with the following:

SELECT 210 
  FROM DUAL

...to get:

210.00 

...instead of 210?

Using pl/sql oracle 10g

Solution 1

SELECT TO_CHAR(210, '999.99') FROM dual;

More to_char related formats here.

Solution 2

Use:

SELECT CAST(210 AS NUMBER(3,2))
  FROM DUAL

...to get the value as a numeric data type. TO_CHAR with a filter would work, but returns the output as a string.

Reference:

• CAST
• TO_CHAR

Solution 3

You can use the to_char function passing in an optional format string e.g. to_char(210.00,'000.00') would give the desired result. Do a google search for "number formatting in oracle" and you will find some good examples of format strings.

Edit Look for "Number Format Elements" here.