CODEWITHSUNDEEP
oracle-database
Linto
Linto

Reputation: 51

number format in oracle

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?

Upvotes: 2

Views: 2727

Answers (5)

APC
APC

Reputation: 146239

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>

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52346

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"')

Upvotes: 1

b.roth
b.roth

Reputation: 9542

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.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 2

Related Questions