Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Simple Numeric Formatting

    Hi,

    With this example

    SELECT 3000 MY_NBR FROM SYSIBM.SYSDUMMY1

    How can I get the output of MY_NBR to include the comma thousands separator like 3,000?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How you see the data is a function of what you are using, not DB2. If you are using the CLP, then numbers are just that--numbers. If you want a comma, then YOU need to convert it with some function (UDF).

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    Was that supposed to be helpful?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DB2 has no mechanism built in that will format the number the way you want. If you want it displayed differently, then you have to convert it. I am sorry if you think this is not helpful, but that is the way it is.

    Andy

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    Thank you Andy, your second response was helpful!

    Not what I wanted of course, but at least I know I can quit searching for a formatting function and create my own.

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

    Arrow Nothing is new under Moon

    Quote Originally Posted by WindInFace View Post
    Hi,

    With this example

    SELECT 3000 MY_NBR FROM SYSIBM.SYSDUMMY1

    How can I get the output of MY_NBR to include the comma thousands separator like 3,000?
    You can find the answers on your question here:

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

    Where I together with tonkuma made the different models of this process.

    Lenny

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

    Exclamation No recursive SQL at all

    Solution without recursive SQL (you can use the part of the main SELECT, depending on the maximum length of your number):
    Code:
    select 
    case length(integer_part)
         when 3
         then sign || strip(integer_part, l, '0') || fraction
         when 6
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ','  
                   || substr(integer_part, 4, 3)  || fraction 
         when 9
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ','     
                   || substr(integer_part, 7, 3)  || fraction
         when 12
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ','
                   || substr(integer_part, 7, 3)  || ','      
                   || substr(integer_part, 10, 3) || fraction
         when 15
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ','             
                   || substr(integer_part, 13, 3) || fraction 
         when 18
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','                         
                   || substr(integer_part, 16, 3) || fraction 
         when 21
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ','                                                
                   || substr(integer_part, 19, 3) || fraction
         when 24
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','                                                                                               
                   || substr(integer_part, 22, 3) || fraction  
         when 27
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','                                                                                                     
                   || substr(integer_part, 25, 3) || fraction  
         when 30
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','              
                   || substr(integer_part, 25, 3)|| ','                                                                                                     
                   || substr(integer_part, 28, 3) || fraction 
         when 33
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','              
                   || substr(integer_part, 25, 3) || ','
                   || substr(integer_part, 28, 3) || ','
                   || substr(integer_part, 31, 3) || fraction                                                                                              
    end formatted_number
    
    from (
    select  
    case when mod(length(integer_part), 3) >= 1
         then repeat('0', 3 - mod(length(integer_part), 3)) || integer_part
         else integer_part
    end  integer_part, 
    fraction, sign
    from
    (
    select 
    case when posstr(in_chr_nbr, '.') > 1 
         then strip(substr(in_chr_nbr, 1, posstr(in_chr_nbr, '.') - 1))
         else strip(in_chr_nbr)  
    end integer_part,
    case when posstr(in_chr_nbr, '.') > 1 
         then substr(in_chr_nbr, posstr(in_chr_nbr, '.')) 
         else ''
    end  fraction,
    sign
    
    from 
    (select 
    case when input_nbr >= 0 
         then varchar(input_nbr) 
         else substr(varchar(input_nbr), 2)
    end in_chr_nbr,
    case when input_nbr >= 0 then ''
         else '-'
    end sign
    from (select -3000111222333444555666777888.999 input_nbr 
            from sysibm.sysdummy1) N1 ) N2 ) N3 ) N4
    Result:

    FORMATTED_NUMBER
    -3,000,111,222,333,444,555,666,777,888.999
    Lenny

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

    Thumbs up Improved formula. Doesn't use recursive SQL.

    Improved formula doesn't use recursive SQL:

    Code:
    select  
    case 
    when integer_part <= '000' 
    then sign || '0' || fraction
    else
    case
    length(integer_part)
         when 3
         then sign || strip(integer_part, l, '0') || fraction
         when 6
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ','  
                   || substr(integer_part, 4, 3)  || fraction 
         when 9
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ','     
                   || substr(integer_part, 7, 3)  || fraction
         when 12
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ','
                   || substr(integer_part, 7, 3)  || ','      
                   || substr(integer_part, 10, 3) || fraction
         when 15
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ','             
                   || substr(integer_part, 13, 3) || fraction 
         when 18
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','                         
                   || substr(integer_part, 16, 3) || fraction 
         when 21
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ','                                                
                   || substr(integer_part, 19, 3) || fraction
         when 24
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','                                                                                               
                   || substr(integer_part, 22, 3) || fraction  
         when 27
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','                                                                                                     
                   || substr(integer_part, 25, 3) || fraction  
         when 30
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','              
                   || substr(integer_part, 25, 3) || ','                                                                                                     
                   || substr(integer_part, 28, 3) || fraction 
         when 33
         then sign || strip(substr(integer_part, 1, 3), l, '0') || ',' 
                   || substr(integer_part, 4, 3)  || ',' 
                   || substr(integer_part, 7, 3)  || ',' 
                   || substr(integer_part, 10, 3) || ',' 
                   || substr(integer_part, 13, 3) || ','  
                   || substr(integer_part, 16, 3) || ',' 
                   || substr(integer_part, 19, 3) || ','
                   || substr(integer_part, 22, 3) || ','              
                   || substr(integer_part, 25, 3) || ','
                   || substr(integer_part, 28, 3) || ','
                   || substr(integer_part, 31, 3) || fraction                                                                                                                                                                                                                                                                                                                                       
     
    end end formatted_number
    from (
    select  
    case when mod(length(integer_part), 3) >= 1
         then repeat('0', 3 - mod(length(integer_part), 3)) || integer_part
         else integer_part
    end  integer_part, 
    fraction, sign
    from
    (
    select 
    case when posstr(in_chr_nbr, '.') > 1 
         then strip(substr(in_chr_nbr, 1, posstr(in_chr_nbr, '.') - 1))
         else strip(in_chr_nbr)  
    end integer_part,
    case when posstr(in_chr_nbr, '.') > 1 
         then strip(substr(in_chr_nbr, posstr(in_chr_nbr, '.')), t, '.') 
         else ''
    end  fraction,
    sign
    
    from 
    (select 
    case when input_nbr >= 0 
         then varchar(0 + input_nbr) 
         else substr(varchar(0 + input_nbr), 2)
    end in_chr_nbr,
    case when input_nbr >= 0 then ''
         else '-'
    end sign
    from (select -3000111222333444555666777888.123 input_nbr 
            from sysibm.sysdummy1) N1 ) N2 ) N3 ) N4;
    Result the same:

    FORMATTED_NUMBER
    -3,000,111,222,333,444,555,666,777,888.123
    Lenny

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Alright Lenny let's see you show off now. How about in written format? For instance:
    one million, three hundred ninety-five thousand, one hundred twenty-two.

    Dave

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

    Exclamation You can do it

    Quote Originally Posted by dav1mo View Post
    Alright Lenny let's see you show off now. How about in written format? For instance:
    one million, three hundred ninety-five thousand, one hundred twenty-two.

    Dave
    How you know that's easy !

    Lenny

  11. #11
    Join Date
    Jul 2009
    Posts
    150

    Wink -3,000,111,222,333,444,555,666,777,888.123

    Quote Originally Posted by dav1mo View Post
    Alright Lenny let's see you show off now. How about in written format? For instance:
    one million, three hundred ninety-five thousand, one hundred twenty-two.

    Dave
    Can you read the number -3,000,111,222,333,444,555,666,777,888.123 for me ?

    Thank you. Kara.
    Last edited by DB2Plus; 04-29-10 at 00:08.

Posting Permissions

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