Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: select query to format a number field to comma seperated

    Hi Team,

    Please help me in writing a select query in db2 to format a number field to comma seperated values.

    For example: if "AMOUNT = 123456.78", THEN THE SELECT QUERY SHOULD RETURN "AMOUNT = 123,456.78".

    Thanks,
    Srini

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    9.7 you have varchar_format
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Hi Rahul,

    Thanks for the post, our db2 version is 9.5, any idea how to implement the same in this version.

    Thanks,
    Srini

  4. #4
    Join Date
    Jul 2009
    Posts
    150

    Thumbs up Answer

    Quote Originally Posted by srinivasa.g82
    Hi Team,

    Please help me in writing a select query in db2 to format a number field to comma seperated values.

    For example: if "AMOUNT = 123456.78", THEN THE SELECT QUERY SHOULD RETURN "AMOUNT = 123,456.78".

    Thanks,
    Srini
    The answer on your question is here:

    http://www.dbforums.com/db2/1647551-...al-string.html

    Kara S.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post Extracted Query

    You can use extracted from my old topic query:

    Code:
    With 
    Input (number) as
    (select 123456.78  
        from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(abs(number)), varchar('', 150), 
            abs(number) - int(abs(number)), 12 
      from    Input 
      where abs(number) >= 1000   
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                     case when number < 0 then '-' else '' end 
                  || strip(digits(int(rem / power(10, k - 3))), l, '0') 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0 and abs(number) >= 1000  
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where abs(number) < 1000  
    )
    select num "Input.................", 
             trans "Result"
      from transform_1 tr where k = 0
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In the thread, I wrote:
    Still SQL0347W was issued.
    (I tested on DB2 9.7 on Windows.)

    To supress the warning message, I changed "where k - 3 >= 0 ..." to "where k >= 3 ...".
    And, it will be better to add "number = num" for multiple Input.

    Here is a result:
    (I added the result of VARCHAR_FORMAT for comparison.
    There were two different points.
    1) VARCHAR_FORMAT keeps left blanks.
    2) VARCHAR_FORMAT keeps integer zero("0") for numbers "ABS(number) < 1.0".)
    Code:
    ------------------------------ Commands Entered ------------------------------
    With 
    Input (number) as
    (VALUES
              12345678.90, 1234567.89, 123456.78, 12345.67, 1234.56
            , 123.45, 12.34, 1.23, 1.0, 0.12, 0.07, 0.0
            , -123.45, -12.34, -1.23, -1.0, -0.12, -0.07
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(abs(number)), varchar('', 150), 
            abs(number) - int(abs(number)), 12 
      from    Input 
      where abs(number) >= 1000   
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                     case when number < 0 then '-' else '' end 
                  || strip(digits(int(rem / power(10, k - 3))), l, '0') 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where number = num
      AND k >= 3 and abs(number) >= 1000  
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where abs(number) < 1000  
    )
    select num "Input.................", 
           VARCHAR(VARCHAR_FORMAT(num, '999,999,999.99'), 20) varchar_format,
             trans "Result"
      from transform_1 tr where k = 0;
    ------------------------------------------------------------------------------
    
    Input................. VARCHAR_FORMAT       Result                                                                                                                                                
    ---------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
               12345678.90   12,345,678.90      12,345,678.90                                                                                                                                         
                1234567.89    1,234,567.89      1,234,567.89                                                                                                                                          
                 123456.78      123,456.78      123,456.78                                                                                                                                            
                  12345.67       12,345.67      12,345.67                                                                                                                                             
                   1234.56        1,234.56      1,234.56                                                                                                                                              
                    123.45          123.45      123.45                                                                                                                                                
                     12.34           12.34      12.34                                                                                                                                                 
                      1.23            1.23      1.23                                                                                                                                                  
                      1.00            1.00      1.00                                                                                                                                                  
                      0.12            0.12      .12                                                                                                                                                   
                      0.07            0.07      .07                                                                                                                                                   
                      0.00            0.00      .00                                                                                                                                                   
                   -123.45         -123.45      -123.45                                                                                                                                               
                    -12.34          -12.34      -12.34                                                                                                                                                
                     -1.23           -1.23      -1.23                                                                                                                                                 
                     -1.00           -1.00      -1.00                                                                                                                                                 
                     -0.12           -0.12      -.12                                                                                                                                                  
                     -0.07           -0.07      -.07                                                                                                                                                  
    
      18 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This is an expression generating same formatted strings as VARCHAR_FORMAT().
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    Input(number) AS (
    VALUES
      123456789012.34, 12345678901.23, 1234567890.12, 123456789.01
    , 12345678.90, 1234567.89, 123456.78, 12345.67, 1234.56
    , 123.45, 12.34, 1.23, 1.00, 0.12, 0.07, 0.00
    , -123456789012.34, -12345678901.23, -1234567890.12, -123456789.01
    , -12345678.90, -1234567.89, -123456.78, -12345.67, -1234.56
    , -123.45, -12.34, -1.23, -1.00, -0.12, -0.07
    )
    SELECT number
         , VARCHAR(VARCHAR_FORMAT(number, '999,999,999,999.99'), 30) AS varchar_format
         , SUBSTR( '               '
                 , 1
                 , 15 - INT(POSSTR(CHAR(ABS(number)),'.')*1.33-0.33)
                      + CASE WHEN ABS(number) < 1.0 THEN 0 ELSE 1 END
                 ) ||
           CASE WHEN number      < 0.0 THEN '-' ELSE ' ' END ||
           CASE WHEN ABS(number) < 1.0 THEN '0' ELSE ''  END ||
           TRANSLATE( SUBSTR('abc,def,ghi,jkl.mn',17 - INT(POSSTR(CHAR(ABS(number)),'.')*1.33-0.33))
                    , CHAR(ABS(number))
                    , SUBSTR('abcdefghijkl.mn',14 - POSSTR(CHAR(ABS(number)),'.'))
                    ) AS case_and_translate
      FROM Input
     ORDER BY
           number DESC
    ;
    ------------------------------------------------------------------------------
    
    NUMBER           VARCHAR_FORMAT                 CASE_AND_TRANSLATE                 
    ---------------- ------------------------------ -----------------------------------
     123456789012.34  123,456,789,012.34             123,456,789,012.34                
      12345678901.23   12,345,678,901.23              12,345,678,901.23                
       1234567890.12    1,234,567,890.12               1,234,567,890.12                
        123456789.01      123,456,789.01                 123,456,789.01                
         12345678.90       12,345,678.90                  12,345,678.90                
          1234567.89        1,234,567.89                   1,234,567.89                
           123456.78          123,456.78                     123,456.78                
            12345.67           12,345.67                      12,345.67                
             1234.56            1,234.56                       1,234.56                
              123.45              123.45                         123.45                
               12.34               12.34                          12.34                
                1.23                1.23                           1.23                
                1.00                1.00                           1.00                
                0.12                0.12                           0.12                
                0.07                0.07                           0.07                
                0.00                0.00                           0.00                
               -0.07               -0.07                          -0.07                
               -0.12               -0.12                          -0.12                
               -1.00               -1.00                          -1.00                
               -1.23               -1.23                          -1.23                
              -12.34              -12.34                         -12.34                
             -123.45             -123.45                        -123.45                
            -1234.56           -1,234.56                      -1,234.56                
           -12345.67          -12,345.67                     -12,345.67                
          -123456.78         -123,456.78                    -123,456.78                
         -1234567.89       -1,234,567.89                  -1,234,567.89                
        -12345678.90      -12,345,678.90                 -12,345,678.90                
       -123456789.01     -123,456,789.01                -123,456,789.01                
      -1234567890.12   -1,234,567,890.12              -1,234,567,890.12                
     -12345678901.23  -12,345,678,901.23             -12,345,678,901.23                
    -123456789012.34 -123,456,789,012.34            -123,456,789,012.34                
    
      31 record(s) selected.
    Last edited by tonkuma; 10-01-09 at 09:12.

Posting Permissions

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