Oracle PL/SQL/Data Type/Numeric Formatting

Number Formatting

   <source lang="sql">

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.

</source>
   
  

TO_CHAR(0012345.6700, "FM99999.99")

   <source lang="sql">

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

---

12345.67 SQL>

      </source>
   
  

TO_CHAR(0.67, "B9.99")

   <source lang="sql">

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

---

 .67

SQL> SQL> SQL>

      </source>
   
  

TO_CHAR(12345.67, "099,999.99")

   <source lang="sql">

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

---

012,345.67

SQL> SQL>

      </source>
   
  

TO_CHAR(12345.67, "$99,999.99")

   <source lang="sql">

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

---

$12,345.67

SQL> SQL>

      </source>
   
  

TO_CHAR(12345.67, "99,999.9900")

   <source lang="sql">

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

---

12,345.6700

SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

-12,345.67 SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

SQL> SQL> SQL> SELECT TO_CHAR(12345.67, "99999.99EEEE") FROM dual; TO_CHAR(12

---

 1.23E+04

SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

12,345.67- SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

<12,345.67> SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

12345.67

SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

  123,46

SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

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

---

1234567

SQL> SQL>

      </source>
   
  

TO_CHAR(12345.67, "C99,999.99")

   <source lang="sql">

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

---

    USD12,345.67

SQL> SQL> SQL>

      </source>
   
  

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

   <source lang="sql">

SQL> SQL> SQL> SELECT TO_CHAR(12345.67, "L99,999.99" ) FROM dual; TO_CHAR(12345.67,"L9

---

         $12,345.67

SQL> SQL>

      </source>
   
  

TO_CHAR(12345.67, "TM")

   <source lang="sql">

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

---

12345.67 SQL>

      </source>
   
  

TO_CHAR(12345.67, "U99,999.99")

   <source lang="sql">

SQL> SQL> SQL> SELECT TO_CHAR(12345.67, "U99,999.99") FROM dual; TO_CHAR(12345.67,"U9

---

         $12,345.67

SQL>

      </source>
   
  

TO_CHAR(2007, "RN")

   <source lang="sql">

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

---

         MMVII

SQL>

      </source>
   
  

TO_CHAR(product_price, "$9,999.00")

   <source lang="sql">

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.

</source>