Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    8

    Custom format number

    Hi guys,

    i need to do something like this in DB2 and oracle. I have a String which actually is a number(upto 2 decimal places) converted to string. i need to format then number according to the delimiter i have.

    eg1: number is 1234567890. i need to format like
    1,234,567,890
    eg2 123456789.09 -> 123,456,789.09


    is there any built in function(which i dont think) or a UDF which can accomplish this task ?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    Is this what you are looking for?
    Code:
    SQL> select to_char('1234567890', '999,999,999,990.00') result from dual;
    
    RESULT
    -------------------
       1,234,567,890.00
    
    SQL> select to_char('123456789.09', '999,999,999,990.00') result from dual;
    
    RESULT
    -------------------
         123,456,789.09
    
    SQL>

  3. #3
    Join Date
    Dec 2006
    Posts
    8
    thanks littlefoot !! but there is a prob with this. for TO_CHAR to work properly, i need to know the format before i run the statement. ie

    in a statement like "select to_char('123456789.09', '999,999,999,990.00') result from dual;", I need to know the format "'999,999,999,990.00'". in my case, i donno whther the number coming is 5 digit or 3 digit or more/less. also i dont know whther there is a decimal point or not. i need to check these things as well. anyways thanks for the time.. do reply if u have a solution for this...

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,434
    make the format big enough to handle any possible number and then left trim. For example,

    select ltrim(to_char('123456789.09', '999,999,999,999,999,999,999,999,990.00')) result from dual;

    RESULT
    ---------------------------------------
    123,456,789.09
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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