1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL: Format Numbers with Commas

Discussion in 'SQL PL/SQL' started by nicky_river, Oct 11, 2010.

  1. nicky_river

    nicky_river Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    What is the inbuilt function in Oracle to display the number in comma format for example,

    I want to display 99999 as 99,999 in oracle. How do I achieve this in Oracle and what is the syntax ?

    Thanks !!!
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Re: Function Issue

    Use TO_CHAR function with format mask


    Code (SQL):
    SQL> SELECT to_char('99999','99,999,999') FROM dual;

    TO_CHAR('99
    -----------
         99,999
     
  3. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Re: Function Issue

    Try this

    select TO_CHAR('123456789.11','999,999,999.99')
    from dual;
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Function Issue

    I think that is not what the poster wants. SQL*Plus offers the column statement to define column-specific formats without using TO_CHAR:

    Code (SQL):
     
    SQL> COLUMN sal format 999,999,990.90
    SQL>
    SQL> SELECT empno, ename, sal
      2  FROM emp;
         EMPNO ENAME                  SAL
    ---------- ---------- ---------------
          7369 SMITH               800.00
          7499 ALLEN             1,600.00
          7521 WARD              1,250.00
          7566 JONES             2,975.00
          7654 MARTIN            1,250.00
          7698 BLAKE             2,850.00
          7782 CLARK             2,450.00
          7788 SCOTT             3,000.00
          7839 KING              5,000.00
          7844 TURNER            1,500.00
          7876 ADAMS             1,100.00
         EMPNO ENAME                  SAL
    ---------- ---------- ---------------
          7900 JAMES               950.00
          7902 FORD              3,000.00
          7934 MILLER            1,300.00
    14 ROWS selected.
    SQL>

     
     
  5. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    Re: Function Issue

    Select to_char(99999,'99g999d99') from dual
    /
     
    Sadik likes this.