Oracle PL/SQL/Data Type/Numeric Formatting

Number Formatting

 
 
Character       Example       Explanation          
9               9999          Each character represents a place holder for digits.    
0               999.00        Used for leading or trailing zeros.         
$               $9999         prefixes the number with a dollar sign.         
S               S9999         Useful for positive and negative numbers in query results.         
PR              9999PR        Displays negative numbers in angle brackets.         
D or .          99D99, 99.99  Places a decimal point in the place where the D or. is.         
,               9,999         Places a comma in the place where the , is.         
RN or rn        RN            Displays roman numerals, in upper-or lowercase depending on the case of the format mask.         
DATE            date          Assumes the number in a Julian date, and displays the resulting value in MM/DD/YY format.

TO_CHAR(0012345.6700, "FM99999.99")

SQL>
SQL>
SQL> SELECT TO_CHAR(0012345.6700, "FM99999.99") FROM dual;
TO_CHAR(0
---------
12345.67
SQL>

TO_CHAR(0.67, "B9.99")

SQL>
SQL>
SQL> SELECT TO_CHAR(0.67, "B9.99") FROM dual;
TO_CH
-----
  .67
SQL>
SQL>
SQL>

TO_CHAR(12345.67, "099,999.99")

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "099,999.99") FROM dual;
TO_CHAR(123
-----------
 012,345.67
SQL>
SQL>

TO_CHAR(12345.67, "$99,999.99")

SQL>
SQL> SELECT TO_CHAR(12345.67, "$99,999.99") FROM dual;
TO_CHAR(123
-----------
 $12,345.67
SQL>
SQL>

TO_CHAR(12345.67, "99,999.9900")

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "99,999.9900") FROM dual;
TO_CHAR(1234
------------
 12,345.6700
SQL>

TO_CHAR(-12345.67, "99,999.99") (2)

SQL>
SQL>
SQL> SELECT TO_CHAR(-12345.67, "99,999.99")  FROM dual;
TO_CHAR(-1
----------
-12,345.67
SQL>
SQL>

TO_CHAR(12345.67, "99999.99EEEE"): scientific notation

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "99999.99EEEE") FROM dual;
TO_CHAR(12
----------
  1.23E+04
SQL>
SQL>

TO_CHAR(-12345.67, "99,999.99MI")

SQL>
SQL> SELECT TO_CHAR(-12345.67, "99,999.99MI") FROM dual;
TO_CHAR(-1
----------
12,345.67-
SQL>
SQL>

TO_CHAR(-12345.67, "99,999.99PR"): negative value in angle brackets

SQL>
SQL>
SQL>
SQL> SELECT TO_CHAR(-12345.67, "99,999.99PR") FROM dual;
TO_CHAR(-12
-----------
<12,345.67>
SQL>

TO_CHAR(12345.67, "99999D99"): decimal point

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "99999D99") FROM dual;
TO_CHAR(1
---------
 12345.67
SQL>
SQL>

TO_CHAR(12345.67, "99999G99"): group separator

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "99999G99") FROM dual;
TO_CHAR(1
---------
   123,46
SQL>
SQL>

TO_CHAR(12345.67, "99999V99"): shift specified number of digits

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "99999V99") FROM dual;
TO_CHAR(
--------
 1234567
SQL>
SQL>

TO_CHAR(12345.67, "C99,999.99")

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "C99,999.99") FROM dual;
TO_CHAR(12345.67,
-----------------
     USD12,345.67
SQL>
SQL>
SQL>

TO_CHAR(12345.67, "L99,999.99" ): currency symbol

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "L99,999.99" ) FROM dual;
TO_CHAR(12345.67,"L9
--------------------
          $12,345.67
SQL>
SQL>

TO_CHAR(12345.67, "TM")

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "TM") FROM dual;
TO_CHAR(12345.67,"TM")
----------------------------------------------------------------
12345.67
SQL>

TO_CHAR(12345.67, "U99,999.99")

SQL>
SQL>
SQL> SELECT TO_CHAR(12345.67, "U99,999.99") FROM dual;
TO_CHAR(12345.67,"U9
--------------------
          $12,345.67
SQL>

TO_CHAR(2007, "RN")

SQL>
SQL>
SQL> SELECT TO_CHAR(2007, "RN") FROM dual;
TO_CHAR(2007,"R
---------------
          MMVII
SQL>

TO_CHAR(product_price, "$9,999.00")

 
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25),
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL> SELECT product_name,
  2         TO_CHAR(product_price, "$9,999.00") "Price",
  3         quantity_on_hand,
  4         last_stock_date
  5  FROM   product;
PRODUCT_NAME              Price      QUANTITY_ON_HAND LAST_STOC
------------------------- ---------- ---------------- ---------
Small Widget                  $99.00                1 15-JAN-03
Medium Widget                 $75.00             1000 15-JAN-02
Product Number                $50.00              100 15-JAN-03
Round Church Station          $25.00            10000
SQL>
SQL> DROP TABLE product;
Table dropped.