Results 1 to 5 of 5

Thread: concat

  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: concat

    Hello group,

    I have the situtation bellow:
    1) code column: char(30) with values '1' to '30'
    2) name column: char(100) with description values for code listed above such as 'Directory', 'Manager', ...

    I need to list both columns in the following way:
    code name
    0001 Directory
    0002 Manager
    .
    .
    .

    0010 Staf
    .
    .
    0030 Operator

    where:
    1) code column should be filled with left zeros (total 4 chars)
    2) name column should be filled with right blank spaces (total 30 chars)

    Thanks in advance,
    Marcos

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: concat

    Originally posted by mberenguel
    1) code column should be filled with left zeros (total 4 chars)
    2) name column should be filled with right blank spaces (total 30 chars)

    May be this will help you to get on the right track:

    http://www.dbforums.com/showthread.php?threadid=973308
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2003
    Posts
    2

    Re: concat

    Hi,

    My platform is AIX 5.1 (FixPack Maintenance ML4) and DB2 7.2 (FixPack 10).
    I wrote the following statement:

    db2 "select repeat ('0', 4 - length (CODE)) CONCAT CODE, cast (NAME as char (30)) from table WHERE NAME IS NOT NULL AND CODE IS NOT NULL ORDER BY CODE" > ${OUTPUT_FILE_DIR}/${EXPORT_DIVISIONS}

    It worked very well but, the content inside the output file is:

    0001 <several blank spaces> Director
    0002 <several blank spaces> Manager
    .
    .
    .
    0030 <several blank spaces> Operator

    and I need the right way:

    0001Director
    0002Manager
    .
    .
    .
    0030Operator

    Is there any thing more to do ?

    Thank you very much again,
    Marcos

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: concat

    You need to specify RTRIM. Revisit the link to the other post it has the example.

    Originally posted by mberenguel
    Hi,

    My platform is AIX 5.1 (FixPack Maintenance ML4) and DB2 7.2 (FixPack 10).
    I wrote the following statement:

    db2 "select repeat ('0', 4 - length (CODE)) CONCAT CODE, cast (NAME as char (30)) from table WHERE NAME IS NOT NULL AND CODE IS NOT NULL ORDER BY CODE" > ${OUTPUT_FILE_DIR}/${EXPORT_DIVISIONS}

    It worked very well but, the content inside the output file is:

    0001 <several blank spaces> Director
    0002 <several blank spaces> Manager
    .
    .
    .
    0030 <several blank spaces> Operator

    and I need the right way:

    0001Director
    0002Manager
    .
    .
    .
    0030Operator

    Is there any thing more to do ?

    Thank you very much again,
    Marcos

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: concat

    Originally posted by mberenguel
    db2 "select repeat ('0', 4 - length (CODE)) CONCAT CODE, cast (NAME as char (30)) from table WHERE NAME IS NOT NULL AND CODE IS NOT NULL ORDER BY CODE" > ${OUTPUT_FILE_DIR}/${EXPORT_DIVISIONS}

    It worked very well but, the content inside the output file is:

    0001 <several blank spaces> Director
    0002 <several blank spaces> Manager
    .
    .
    .
    0030 <several blank spaces> Operator

    and I need the right way:

    0001Director
    0002Manager
    .
    .
    .
    0030Operator

    Is there any thing more to do ?

    You could probably go one step further and put CONCAT operator between CODE and NAME...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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