Numeric Format in SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Numeric Format in SQL

  1. #1
    Join Date
    Jul 2001
    Posts
    334
    Hi,

    How I can get the result in format:
    for example here is a simple query
    select sum(sal) from emp;
    29025

    But I need the result in
    29,025.00

    Thanks

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    col sal format 999,999

    SQL> col num format 999,999.99
    SQL> select * from test;


    NUM
    -----------
    999,999.99


    SQL> insert into test values (10000);

    1 row created.

    SQL> select * from test;


    NUM
    -----------
    10,000.00

  3. #3
    Join Date
    Sep 2001
    Posts
    15
    Hi!

    select to_char(sum(sal), '999,990.99') from emp;
    or
    select to_char(sum(sal), '999G990D99') from emp;


    Victor
    www.dynamicpsp.com

  4. #4
    Join Date
    Jul 2001
    Posts
    334
    Thanks for the solutions, but the problem is I am creating flat spool file and I need the format for example from the left most start position like below:
    0029025.00

    Now if I use to_char function then it leaves 1 space from the left
    0029025.00
    And for some reason we can not change the sqlldr control file, we have to follow the bank standard provided format.

    Please help to resolve this issue.
    * You may not see one space blank left in this page, please run emp query to see what I am trying to explain.

    Thanks in advance,

    aph.




  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    try to use LPAD/ RPAD function
    Cheers!
    Cheers!
    OraKid.

  6. #6
    Join Date
    Jun 2002
    Posts
    22

    Wink

    One space on the left is the sign. If you try to convert negative number with to_char function you will see the minus sign rather than space.

    select 'FORMAT: 000g000d00 PLUS ', to_char(23000.4, '000G000D00') from dual
    union
    select 'FORMAT: 000g000d00 MINUS ',to_char(-23000.4, '000G000D00') from dual
    union
    select 'FORMAT: S000g000d00 PLUS SIGN ',to_char(23000.4, 'S000G000D00') from dual
    union
    select 'FORMAT: S000g000d00 MINUS SIGN',to_char(-23000.4, 'S000G000D00') from dual

    RESULT :
    FORMAT: 000g000d00 MINUS -023.000,40
    FORMAT: 000g000d00 PLUS 023.000,40
    FORMAT: S000g000d00 MINUS SIGN -023.000,40
    FORMAT: S000g000d00 PLUS SIGN +023.000,40


    To avoid the space you can try the 'FM'

    select to_char( 2000,'FM000g000d00') from dual;
    ( try this with negative number)

    I hope this will help you.
    Ao.
    Aleš

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> col a format '09999.999'
    SQL> select 987.76 a from dual;

    A
    ----------
    00987.760

    SQL> col a format '099,999.999'
    SQL> select 987.76 a from dual;

    A
    ------------
    000,987.760


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width