Results 1 to 2 of 2

Thread: Db2 Substr

  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: Db2 Substr

    Running on a mainframe. ZOS, DB2 V7.2.

    database field is defined as
    PSTL_CDE DECIMAL(9) NOT NULL

    I am trying to switch it to a CHAR field to preserve leading zeros. (actually it's a bit more complicated than that, because it's used in a View with another table that's defined as a char.)

    But here is my question. I have several different ways to select and convert the field. Ideally, I want a 9 character field, no decimal point. Just like if it was defined as CHAR(09).

    Here's a few of my results
    ORIG DEC9 = "140631834."

    CHAR(PSTL_CDE) = "140631834." => I don't want the decimal point

    SUBSTR(CHAR(ADDR_PSTL_CDE),1,09) = "14063183" => Missing the '4'.

    SUBSTR(CHAR(ADDR_PSTL_CDE),1,10) = "140631834" => the number I want, but now it's CHAR(10). Not a huge deal, but why do I have to use 10?

    Why do I have to make it a CHAR(10)? The DB2 manual (pg 283, dsnsqh12) says the length parm should be Length - Start + 1 (9-1+1) or 9.

    Syntax is SUBSTR(string, start, length).
    Last edited by edrenckh; 08-18-04 at 11:04.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    I think it has something to do with the CHAR function. According to the SQL reference, for "Decimal to Character" "the lenght of the result is 2+p, where p is the precision of the decimal-expression. (In your case 2+9 = 11)

Posting Permissions

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