# Displaying number before decimal points in oracle

I have a logic where I want to calculate and display numbers based upon some operations. The operations are below

`V_CALPERCENT nvarhcar(100)`, `v_yearMSA1 nvarchar(100)`,

So I want to calculate `V_CALPERCENT` and I have one value for `v_yearMSA1 = 2`

SO here goes my calculation,

``````V_CALPERCENT :=  (v_yearMSA1  * 2.25) / 100
``````

Its returning me as `.05`

where as I want it should also display the number before decimal point. How to get it please help

• The client software is typically responsible for formatting query output; so, what client are we talking about? – mustaccio Jan 12 at 14:37
• @mustaccio: but the same I am trying using dual is giving me the expected format. What do you mean by client software ? – hud Jan 12 at 14:38
• How can you "calculate V_CALPERCENT" when V_CALPERCENT is a character string, not a NUMBER. This is going to require implicit to_num and to_char operations. Use the correct data types. – EdStevens Jan 12 at 14:51
• Why do you store numeric values in `nvarchar(100)` data type - it's almost the worst you can do. – Wernfried Domscheit Jan 12 at 19:03

Example:

``````SQL> select to_char((2 * 2.5) / 100) from dual;

TO_
---
.05

SQL> select to_char((2 * 2.5) / 100, '99990D99') from dual;

TO_CHAR((
---------
0.05

SQL>
``````

Update:

Number example:

``````SQL> select (2 * 2.5) / 100 as mynum from dual;

MYNUM
----------
.05

SQL> col mynum format 99990D99
SQL> select (2 * 2.5) / 100 as mynum from dual;

MYNUM
---------
0.05

SQL>
``````

Number Format Models

• so you mean to say, I need to use `to_char` for that ? – hud Jan 12 at 18:39
• @hud No. I mean to say that you need to format the output with the required format string if you want to store it in a character type. Which you should not do anyway. Having `v_yearMSA1` and `V_CALPERCENT` as char types - that is a huge red flag, this is poor quality code. Numbers should be stored in number types and formatting the output is the clients responsibility. – Balazs Papp Jan 12 at 21:26
• So, shall I take the datatype as number, I tried it with that too but I was not getting any values before decimal.. WHY ? – hud Jan 13 at 10:14
• @hud Because you did not set the proper display format. I added an example. – Balazs Papp Jan 13 at 19:43
• Thanks Balazs, your explanation cleared my most of the doubts.. – hud Jan 14 at 13:40