Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    15

    Unanswered: string function - concat

    I have used concat many times in Oracle and just switched
    over to DB2 personal.

    Does this version support concat and if so how would I
    concat a character field from 1000 to a1000.
    ex:
    update inventory set part = concat( 'a', part);

    inventory - table.
    part - field with 1000 value.

    any ideas/ thanks
    BJ

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    why not using the standard operator:

    update inventory set part = 'a' || part;

    works both on Oracle and DB2 and is SQL standard
    Juliane

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    DB2 also supports the CONCAT scalar function; "||" is actually a shortcut to that function.
    You have to make sure, though, that both arguments of concat (or of "||") are of datatype "text"; if "part" is numeric, use
    Code:
    update inventory set part = concat( 'a', digits(part));
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2006
    Posts
    15

    concat data error

    All works well but when I try to insert a letter on the right hand side of the field' part' value it seems the blank spaces
    create a problem.I increased the spaces from 10 to 15
    but it still gave the same error message.

    Do I have to rtrim blank spaces just before I conact when
    inserting on right side , if so how?
    error message:
    SQL0433N Value "1000 a" is too long. SQLSTATE=22001






    Thanks again
    Bob

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by bobjohnson360
    ... it seems the blank spaces create a problem.
    Do I have to rtrim blank spaces just before I concat
    That's right. Thus:
    Code:
    rtrim(a) || ' and the rest'
    will remove trailing spaces from a before concatenating.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2006
    Posts
    15

    concat data too long error

    Peter


    Works just great !

    Thanks
    BJ

Posting Permissions

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