How to expand decimal places of a number to a minimum in Oracle PLSQL?

Question

I cant figure out how to select the following:

123        -> 123.00000
123.12     -> 123.12000
123.123456 -> 123.123456

I would like to expand the number of decimal places to for example 5 decimal places (minimum) If there are no decimal places at all there should be 5 zeros. It is fine if there are more then 5 decimal places.

SELECT ROUND(123,5) FROM DUAL;

will result: 123 instead of 123.00000

The number has a default precision.

Is this possible or should I convert it to a varchar with the oracle number formats?

I am using Oracle 10g with plsql.

Solution 1

You could use the following:

SQL> SELECT X, to_char(X, 'fm99999999.00000999')
  2    FROM (SELECT 123 X FROM dual UNION ALL
  3          SELECT 123.12 FROM dual UNION ALL
  4          SELECT 123.123456 FROM dual);

         X TO_CHAR(X,'FM99999999.00000999
---------- ------------------------------
       123 123.00000
    123.12 123.12000
123.123456 123.123456

Solution 2

You need to convert it to a Varchar as follows:

SELECT to_char(123, '9999.99999') from dual;

Comments

[Michele La Ferla]

what if I have a decimal number like 00.71? With your method, I would get .71 but I want 0.71.

[Vincent Malgrat]

@dev.mi Use fm99999990.00, see docs.oracle.com/database/121/SQLRF/…