Clever Geek Handbook

Oracle number format

Hai,

I have a problem with format.i'm number using oracle. I have a numeric field in a database. But when I return it I have to be treated as a 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 a result. How can I solve this? Can anyone help me?

+2


source to share


5 answers


You can use a function TO_CHAR

to format data:



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

TO_CHAR(200.00,'FM999G999G990D
------------------------------
200.00
+3


source


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
+2


source


Debit availability implies the need to obtain a loan. In Oracle SQL, we can use the SIGN () function to determine if 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>
+2


source


The answers here that suggest TO_CHAR are correct, but if you call this SQL from your application code:

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

+1


source


Additional characters can be specified as part of the conversion by enclosing them in double quotes, which can make things a little easier:

To_Char(amount,'fm999G999G990D00" DR"')
+1


source






More articles:


All Articles