Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    9

    Question Unanswered: Format Number in DB 2 v 8.2

    Dears,
    I have an integer field in a table and I want when I query this table to display the number formatted as "NNN,NNN,NNN.NN" but I could not find a function to format numbers in DB2.

    Can anyone help?

    Thanks for your time

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    what i can guess is that you want to get o/p like that
    for 123456789 --> 123,456,789
    you can get these using substr,concat function


    C:\Program Files\IBM\SQLLIB\BIN>db2 select concat(concat(concat(concat(substr(ch
    ar(12345678),1,3),','),substr(char(123456789),4,3) ),','),substr(char(123456789),
    7,3)) from sysibm.sysdummy1

    1
    -----------
    123,456,789

    Rahul Singh

  3. #3
    Join Date
    May 2003
    Posts
    9

    RE RE: Format Number in DB 2 v 8.2

    Thanks for the reply but the solutin if only valid if the number is 6 digits which is not valid assumption, as it could be greater or less than these number of digits.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    hi Ashraf,
    but you havnt clearly explained what o\p u desire ??
    ur NNN,NNN,NNN.NN example is just too vague
    now i assume that i\p shld be like.
    1234 --> 1,234
    12345 --> 12,345
    where i\p is supplied at runtime.
    if such case u can write a small proc using multiple cases which can use
    length scalar fn.

    length(ltrim(rtrim(char(7621988))))
    and then can manipulate position of ',' to some condition like multilples of 3.

    there could be better soln or much more specific function but i am not able to think of that

    regards,
    Rahul Singh

Posting Permissions

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