Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: How to convert INT 0 or 10 to 001

    Thanks Thanks Thanks Thanks Thanks

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Right(Digits(your_number), 3) ....

    Lenny

  3. #3
    Join Date
    Sep 2003
    Posts
    32

    Great! Thanks

    I used CAST(mycolumn as CHAR(3)) and it didn't work.

    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What do you mean with "didn't work"?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2003
    Posts
    32
    select cast (1 as char(3))

    result: 1, not 001

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, it returns a string with 3 characters where the first 2 are blanks. So you have to replace those blacks with 0's.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2003
    Posts
    32
    So what's the syntax if using cast to do that?

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

    Question

    Does this not work ?

    Code:
    Select   Right(Digits(your_number), 3)
      from   your_table
    Lenny

  9. #9
    Join Date
    Apr 2009
    Location
    San Jose, CA
    Posts
    1
    The CAST will left justify. e.g. cast(1 as char(3)) will give you '1 '
    I am not sure what your exact question is.
    Are you trying to have leading zeros for any int number or just for a 3 digit number?
    Do you need to handle negative numbers?
    Do you need to handle INT column only or decimal/smallint/ect. as well?
    For instance:

    1 => 001
    10 => 010
    100 => 100
    1000 => ?
    -1 => ?
    1.0 => ?


    If you ever only need up to 3 digits for INT, and do not have negative numbers, you can do:
    SUBSTR( DIGITS(int_number), 8)
    e.g. select substr(digits(1), 8) from sysibm.sysdummy1 => '001'


    --Andrei L.

  10. #10
    Join Date
    Sep 2003
    Posts
    32
    No, Right(Digits(your_number), 3)
    is good for me.

    I only have INT and no negative. I thought Cast(1 as char(3)) will give me 003, now I know it is not.

    Thanks,

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by newer View Post
    No, Right(Digits(your_number), 3)
    is good for me.

    I only have INT and no negative. I thought Cast(1 as char(3)) will give me 003, now I know it is not.

    Thanks,
    Sounds good for me.

    Lenny

Posting Permissions

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