troubled with a simple SELECT

DBA-Jr · #1 (**permalink**) 07-25-06, 17:12

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.

Peter.Vanroose · #2 (**permalink**) 07-28-06, 13:09

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.)

DBA-Jr · #3 (**permalink**) 07-28-06, 18:28

Thanks Peter. But this one still didn't work.

Peter.Vanroose · #4 (**permalink**) 07-29-06, 08:27

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

Internet Services

Web Development

Digital Marketing

Consumer Tech