Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Need help with VARCHAR column

    Hi,

    As per my understanding CHAR specifies a fixed-length column for character string data,The maximum length is 254 characters. while VARCHAR specifies a varying-length column The maximum length is 4000 characters. But I wanted to know if there is a possibility to export the value of the VARCHAR column as fixed-length column to an text file?If I cast this as Char I am export this column data as fixed length, but not able to export the entire value which is 2000 characters. Your help is highly appreciated.

    Thanks
    Cells

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

    Re: Need help with VARCHAR column

    You are right ... You can cast to a char only if the length is less than 255 ...

    You may consider using a combination of substr, length and repeat functions to achieve what you want

    HTH

    Sathyaram

    Originally posted by cells
    Hi,

    As per my understanding CHAR specifies a fixed-length column for character string data,The maximum length is 254 characters. while VARCHAR specifies a varying-length column The maximum length is 4000 characters. But I wanted to know if there is a possibility to export the value of the VARCHAR column as fixed-length column to an text file?If I cast this as Char I am export this column data as fixed length, but not able to export the entire value which is 2000 characters. Your help is highly appreciated.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    Sathyaram, thanks for the reply!

    If possible, could you please provide me an example here? ie., exporting the Varchar column value as fixed length column.

    Thanks,
    Cells

  4. #4
    Join Date
    Jan 2004
    Location
    Egypt
    Posts
    61

    Re: Need help with VARCHAR column

    hi there,

    u can do this by using C program, all u have to do is to select yr varcahr column into host variable, then use C fprinft(fp1,"%2000s \n", hostvar);

    this will print the data with fixed length of 2000 byte even if it contains 4 bytes
    hany heggy,
    IBM certified Professional, AIX system support
    www.melodyhits.tv

  5. #5
    Join Date
    Mar 2004
    Posts
    7
    hanyheggy,
    Thanks for your suggestion! Yes I do agree with you, it can be done using C, but we are using DB2 7.2 on AIX 5.2 platform...we don't use C at all in our environment. Does anyone have an idea how to achieve this using DB2?

  6. #6
    Join Date
    Mar 2004
    Posts
    7
    to present the more information here.., here is the script I am using currently..

    db2 "export to /utility/xfer/table_data.txt of del modified by chardel~ \
    select
    case when DEATAIL_TEXT is not null then cast(DETAIL_TEXT as varchar(2000)) else cast('<NULL>' as varchar(2000)))end

    in the above script I need to export the DETAIL_TEXT column as fixed width column..Your ideas are appreciated!

    Cells

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Hope you wouldn't mind the stupid column names ...

    This generates an output with first column's length as 2000

    HTH

    Sathyaram

    export to exp1.csv of del
    with temp1(colval) as
    (
    values
    ('1'),
    ('2')
    )
    ,
    temp2(c,j) as
    (
    select
    case when colval is not null then colval||repeat(' ',2000-length(colval))
    else
    cast(null as char)
    end,
    '10'
    from temp1
    )
    select * from temp2

    Cheers
    Sathyaram


    Originally posted by cells
    to present the more information here.., here is the script I am using currently..

    db2 "export to /utility/xfer/table_data.txt of del modified by chardel~ \
    select
    case when DEATAIL_TEXT is not null then cast(DETAIL_TEXT as varchar(2000)) else cast('<NULL>' as varchar(2000)))end

    in the above script I need to export the DETAIL_TEXT column as fixed width column..Your ideas are appreciated!

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

  8. #8
    Join Date
    Mar 2004
    Posts
    7
    Sathyaram,

    I used the Substr function as you mentioned in your previous post..It worked! thanks much for the help.

Posting Permissions

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