number format in oracle

Question

Hai,

i have a problem with number format.i'm using oracle. I have a number field in database.But when i retreive it i need to be seen as floating point number For example: while retreiveing,now i got the result as 200 DR (DR for Debit,it is given manually). Now i need to get the result as 200.00 DR as the result. How can i solve this?Can any one help me?

Answer 1

Hi Linto,

You can use the TO_CHAR function to explicitely format data:

SQL> SELECT to_char(200.00, 'fm999G999G990D00') FROM dual;

TO_CHAR(200.00,'FM999G999G990D
------------------------------
200.00

Answer 2

Use TO_CHAR like this:

to_char(number,'999999.99')

For example:

SQL> select to_char(1234.5678,'999999.99')||' DR' as display from dual;

DISPLAY
-------------
   1234.57 DR

Answer 3

The answers here that suggested TO_CHAR are correct, but if you're calling this SQL from the application code:

Get the number without formatting it with the SQL and then use your programming language to format it. For example, in Java, use the DecimalFormat class. In other words, leave formatting for the application specific code, not for the SQL.

Answer 4

The additional characters can be specified as part of the conversion by enclosing them in double-quotes, which might make things a little more simple:

To_Char(amount,'fm999G999G990D00" DR"')

Answer 5

The presence of a debit implies the need for a credit. In Oracle SQL we can use the SIGN() function to tell whether a number is positive or negative...

SQL> select to_char(abs(amt), 'fm999g999d00')||' '
  2              ||case when sign(amt) = -1 then 'DR' else 'CR' end as fmt_amt
  3  from transactions
  4  order by txn_ts, txn_id
  5  /

FMT_AMT
--------------
200.00 CR
200.00 DR
788.67 CR
788.67 DR

SQL>