MVSFORUMS.com

ravikirti

I am trying to select two fields .. First_name and Last_name
i have been able to concat the two but am not able to remove the spaces in between.

For ex If the length of both the fields is 5

First name Last Name
RA DEV
SH PPP

The output for the concat command gives me the following
select concat(first_name,last_name) from tablename;
RA DEV
SH PPP
and the output for which i am looking is
RA DEV
SH PPP

Hope somebody replies to me as soon as possible.

Thanks
Ravi

ravikirti · Posted: Wed Dec 22, 2004 4:38 am Post subject:

am trying to select two fields .. First_name and Last_name
i have been able to concat the two but am not able to remove the spaces in between.

For ex If the length of both the fields is 5

the dot indicates blank spaces
First name Last Name
RA... DEV...
SH... PPP...

The output for the concat command gives me the following
select concat(first_name,last_name) from tablename;
RA... DEV...
SH... PPP...

and the output for which i am looking is
RA DEV
SH PPP

Hope somebody replies to me as soon as possible.

Thanks
Ravi

kolusu · Posted: Wed Dec 22, 2004 5:17 am Post subject:

Ravikirti,

You should use the RTRIM function which removes the trailing spaces from a column.

try this sql

ravikirti · Posted: Wed Dec 22, 2004 5:46 am Post subject:

Thanks ..it worked...............

regards
ravikirti

Phantom · Posted: Wed Dec 22, 2004 6:02 am Post subject:

Thanks for the solution Kolusu.

Phantom

NASCAR9 · Posted: Wed Dec 22, 2004 11:55 am Post subject:

This will also work:

SELECT RTRIM(LNAME) || ' '||
SUBSTR(FNAME,1,12) AS NAME
FROM TABLE
WHERE ....

Phantom · Posted: Wed Dec 22, 2004 12:12 pm Post subject:

NASCAR9,

I'm not that familiar with DB2. I always use the pipe symbol '||' for concatenation in REXX. Is this the same in DB2 ?. How did u chose the length of FNAME to be 12 in ur code. Is that just an example to work with SUBSTR ?

Anyway the good thing is that, I learnt something new today. Thanks for that.

Regards,
Phantom

kolusu · Posted: Wed Dec 22, 2004 12:18 pm Post subject:

Phantom · Posted: Wed Dec 22, 2004 12:37 pm Post subject:

Thanks for the clarification kolusu,

Regards,
Phantom

NASCAR9 · Posted: Wed Dec 22, 2004 2:23 pm Post subject:

Phantom,

FNAME is just an example. A VB programmer in the shop showed me this select. The kid is Very sharp.

NASCAR9