Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    12

    Unanswered: Splitting of value

    Hi There,

    Can anybody please tell me how do I split the value in a column into two separate values and load the resulting values in two different columns...???

    Thanks,
    Shyam.

    P.S: I need to split the values in a column of length 16 into 4 & 12 respectively. Then I need to load these two values into two separate columns.

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

    Re: Splitting of value

    Try this one :

    export to splitfile.del of del select col1,left(col16,4),substr(col16,5) from table1"

    This can then be loaded into a target table

    Cheers
    Sathyaram

    Originally posted by shyams75
    Hi There,

    Can anybody please tell me how do I split the value in a column into two separate values and load the resulting values in two different columns...???

    Thanks,
    Shyam.

    P.S: I need to split the values in a column of length 16 into 4 & 12 respectively. Then I need to load these two values into two separate columns.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    12

    Re: Splitting of value

    Hi Sathyaram,

    This is what I tried,

    export to splitfile.del of del select bnf_code,left(bnf_code,16,4),substr(bnf_code,16,5) from sample1

    where bnf_code is the col name and sample1 is the table name.

    It did not work and I got the following error messages,

    SQL3022N An SQL error "-440" occurred while processing the SELECT string in
    the Action String parameter.

    SQL0440N No authorized routine named "LEFT" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Can you please let me now what i sthe mistake...???

    Thanks,
    Shyam.

    Originally posted by sathyaram_s
    Try this one :

    export to splitfile.del of del select col1,left(col16,4),substr(col16,5) from table1"

    This can then be loaded into a target table

    Cheers
    Sathyaram

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    Re: Splitting of value

    How about:

    >db2 select * from xyz
    C1
    ----------------
    abcdefghijklmnop

    E:\SQLLIB>db2 export to splitfile.del of del select substr(c1,1,4), substr(c1,5,12) from xyz

    >more splitfile.del
    "abcd","efghijklmnop"

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

    Re: Splitting of value

    Shyam

    The problem is LEFT and RIGHT can take only two arguments ... First is the string and the second is the length ..

    My apologies from calling a 16 byte column col16 which has been misinterpreted as col,16

    LEFT(colname,4) should return the first four characters of the column

    HTH

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

Posting Permissions

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