Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: troubled with a simple SELECT

    Hi All
    I am using DB2 V8.1 on linux.
    I have a table as below:

    Code:
     
    CREATE TABLE AL_TABLE_EXT (LEN INT, TEXT COLB (2M));
    Column LEN is the actual length of TEXT. Now I need to return TEXT with trailing spaces truncated. If LEN = 100, What I need is EXACTLY the result of following:

    Code:
    SELECT LEN, SUBSTR(TEXT,1,100) AS TEXT FROM AL_TABLE_EXT;
    I definitely can not hard-code 100 in my application, so I tried the following:

    Code:
     
    SELECT LEN, SUBSTR(TEXT,1,LEN) AS TEXT FROM AL_TABLE_EXT;
    The second one did not give me a sub-string, but a whole length of string (with trailing spaces). I also tried function RTRIM. The result was the same.
    It seems a simple thing, but I just can't make it exactly as I need.

    Could you please advise me? Thank you in advance.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Try this:
    Code:
    SELECT LEN, CAST(SUBSTR(TEXT,1,LEN) AS VARCHAR) AS TEXT FROM AL_TABLE_EXT
    (I did not check its working, though.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    Thanks Peter. But this one still didn't work.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    After some trial-and-error, the following seems to work with DB2 v8.1 for Windows:
    Code:
    select cast(rtrim(text) AS varchar(32672)) from al_table_ext
    or alternatively
    Code:
    select cast(substr(text,1,len) AS varchar(32672)) from al_table_ext
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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