CODEWITHSUNDEEP
oracle-databaseplsql
Remya
Remya

Reputation: 103

Oracle number to decimal

How I can select a number value in decimal, just like 12 to 12.00, without using to_char in oracle?

Upvotes: 2

Views: 23930

Answers (3)

Shailesh
Shailesh

Reputation: 990

You can use 

Select To_char(ID,'9990.99') from temp

0 is must for the formatting. If you want 0.00 value (eg:0.10,0.06) you have to put 0 inside the format without 0 it will look like this .00(eg:.10,.06). Check it out here

Without 0

Select To_char(0,'999.99') from Dual

With 0

Select To_char(0,'9990.99') from Dual

Upvotes: 2

Mark Bowytz
Mark Bowytz

Reputation: 1362

If you're using SQL*Plus, you can format the output by changing the column format:

SQL> desc temp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FIELD1                                             VARCHAR2(100)
 FIELD2                                             VARCHAR2(100)

SQL> select * from temp;

        ID FIELD1     FIELD2
---------- ---------- ----------
         1 hello      yyyy
         2 hello      yyyy

SQL> col id format 999.99
SQL> select * from temp;

     ID FIELD1     FIELD2
------- ---------- ----------
   1.00 hello      yyyy
   2.00 hello      yyyy

Upvotes: 2

btilly
btilly

Reputation: 46497

Cast it to NUMBER(9, 2) (the 9 is arbitrary, use something else if you want) and it will be in that form.

However note that once you put it into your application, the representation is up to whatever language that uses. If it represents that data type as a float, you'll get a float and it will no longer have the format you want. In that case you are better off not formatting it until the number is outside of the database.

Upvotes: 1

Related Questions