Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    9

    Unanswered: Padding with Zeroes

    Hi All,

    I am trying to find the equivalent of lpad in DB2,i need to pad with zeroes instead of blanks as sysfun.left does.
    E.g.:
    (dpt_id,2) shld return 05 if dpt_id =5
    All suggestions welcome.

    Thanx,

  2. #2
    Join Date
    May 2002
    Posts
    8
    Hi,

    There is no analog of Oracle's LPAD(), RPAD() in DB2, but you can create one using Case Expressions.

  3. #3
    Join Date
    May 2002
    Posts
    9
    Hi,

    I am sorry i dont understand,,case expressions meaning...???
    Do you have any examples of the same.

    Rgds

  4. #4
    Join Date
    May 2002
    Posts
    8
    Hi,

    I think you can try something like:

    select file_id, session,length(ltrim(rtrim(char(session)))) as lng_sess,
    case
    when length(ltrim(rtrim(char(session))))=2 then '00'||ltrim(rtrim(char(session)))
    when length(ltrim(rtrim(char(session))))=3 then '0'||char(session)
    when length(ltrim(rtrim(char(session))))=4 then ltrim(rtrim(char(session)))
    end as session
    from fileset


    Thank you.

  5. #5
    Join Date
    May 2002
    Posts
    9
    Hi,

    I c what u mean, thanks, currently i have done something similar to that using if then..elseif..end if
    not very pretty though.

    Thanks for the input.

    Rgds,

  6. #6
    Join Date
    Jun 2002
    Location
    Missouri
    Posts
    1
    Try using this method,

    for turning a 1 char field into a 2 char with leading zeros:

    right ('00' || column_name,2)

    this will allow you to have two characters in your column (ex. column_name = 12 will return 12, column_name = 4 will return 04, column_name = ' ' will return 00, etc.). This is seems to be easier to remember and fairly readable for future debugging.

  7. #7
    Join Date
    May 2002
    Posts
    9

    mapping with zeroes

    Hi,

    This is defnly a much cleaner and elegant solution,thanks.

    Rgds,
    Venky

  8. #8
    Join Date
    Oct 2012
    Posts
    1
    Thanks for the tip.
    One quick note, make sure you trim the original value.

    right ('00' || Trim(column_name),2)

Posting Permissions

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