Link to home
Start Free TrialLog in
Avatar of diteps06
diteps06Flag for United States of America

asked on

Convert a number to have 2 decimal places in Oracle

I have a number column whose values I will like to convert into a number with 2 decimal places.
For example the number 9200 should become 92.00
I tried the formula
SELECT TO_NUMBER(TO_CHAR(9200/100, '999.00')) FROM DUAL
      Output is 92
I would have prefer it to be 92.00
The output should be in number format.
Any ideas
Avatar of Geert G
Geert G
Flag of Belgium image

you should format the output on your display
not in the database

round(your_column, 2) as output

Open in new window


see the output formatting of the display environment for options
If you are simply after output then to_char() by itself should be sufficient:

SELECT TO_CHAR(9200/100, '999.99')
FROM DUAL


+----------------------------+
| TO_CHAR(9200/100,'999.99') |
+----------------------------+
|                      92.00 |
+----------------------------+

Open in new window


see it running online here:  https://rextester.com/NRWTG7393
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of diteps06

ASKER

When I divide 9200 by 100 I have 92.
If I use ROUND(92,2) I will still have 92.
What I want is the opposite. I will like to add 2 decimal places to the result. In other words xxx(92,2) should give 92.00
You are right if the number is 9234. Infact, the following statement will be correct: SELECT CAST ((9234/100) AS NUMBER(9,2)) FROM DUAL
  as the result will be 92.34
However,  SELECT CAST ((9200/100) AS NUMBER(9,2)) FROM DUAL will not give me the desired result.
The output will be 92 but I want 92.00
As has been stated previously, you need to remove the TO_NUMBER from what you are doing.

The TO_CHAR will do what you want, which is convert the number to a string and give it a format.  A NUMBER doesn't have a format.

92 = 92.0 = 92.00 = 92.000 = 92.0000

If you are using SQL*Plus, you can set the display mask for the column by doing something like this:

column display_col format 9999.00
SELECT TO_NUMBER(TO_CHAR(9200/100, '999.00')) display_col FROM DUAL;

But, as others mentioned, that is just the front end application that you are using to connect to the database formatting the number for you.  Formatting really isn't the job of the database.  If you want to force the database to do formatting, TO_CHAR is the function to use.
I finally resolved at the front end application. As advised formatting is the job of the front end application. I succeeded at this level calling a  similar  to_char and tonumber functions in SAP Business Objects
You shouldn't need a tonumber function.  It is already a number, converting a number to a number isn't useful.  You only need the tochar function, that is what does the formatting.