Results 1 to 5 of 5

Thread: Left pad values

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Left pad values

    I've never had to do this before so I apologize if this is a rather silly question:

    I want to insert data into a fixed length column - CHAR(22). The values vary in length - some are leass than 22, some exactly 22.

    How can I pad those values that are less than 22 characters in length with spaces so they are exactly 22?

    Thank You
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Left pad values

    If you are using CHAR, DB2 should do the padding for you ...

    Look at the REPEAT function and the LEN function, It could be somethign like

    string1||repeat(' ',22-len(string1))

    If you let the forum know, why you want to do this, there may be a better solution provided

    Sathyaram


    Originally posted by ansonee
    I've never had to do this before so I apologize if this is a rather silly question:

    I want to insert data into a fixed length column - CHAR(22). The values vary in length - some are leass than 22, some exactly 22.

    How can I pad those values that are less than 22 characters in length with spaces so they are exactly 22?

    Thank You
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 will automatically fill with blanks. But it might help to show us the exact SQL statement that you want to use, just to be sure.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Here's the rationale(?) behind my request - this is from a SQL Server developer:

    I created a test SQL Server table on my machine. It has one field defined as a char(22). I inserted all the (non 90,91,92 ) account numbers from the first page of the sample data you provided. When I inserted them I left padded with spaces each entry so it was 22 characters long. When I do a BETWEEN search from 1253 to 20010312, with both begin & end values left padded with spaces out to total 22 characters I get the following results:

    1253
    32432
    32432
    32432
    0005783
    5220011
    9657819
    18720022
    20010312

    If I search on the numbers alone, without left padding them I get no results.

    On the other hand, if I insert the table with the account numbers without left padding with spaces , the above query which left pads the begin and end values, of course, doesn't return any results. And, if I query on the numbers alone, I get the following:

    1253
    153606671011
    18720022
    196625415407
    196625415407
    196625415407
    196625415407
    196625415407
    196625415407
    196625415407
    20010312

    It looks like the first set of results is what you would like to see returned. If Viewpoint is storing the account numbers in a char field and left padding them with spaces then it COULD (depending on whether DB2 acts in the same way as SQL Server) be possible to get those results if the begin and end values passed in for the search are left padded with spaces so they will match what's in the database.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume you are referring to Viewpointe Archive Services?

    If I understand the problem correctly I would just create a new column which is defined as numeric that contains the character converted to numeric. Obviously, you would want an index on the numeric version of account-number.

    You could also try:

    WHERE CAST(account-number AS DECIMAL(22)) BETWEEN 1253 to 20010312

    This will give you the correct answer, but I doubt that an index on account number would be used in b-tree mode (possibly an complete index scan might be used, or a tablespace scan).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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