Mehmet,
If trimming of white spaces is what is really intented, then replace is
the function you want to use.
Translate: Returns a transformed string expression & thus the length of
the return'd string would be same as the original.
Replace: replaces all the occurrences of expr1 in expr2 with expr3

For example:
DB2-CLP Prompt>db2 values "char(replace('text1 text2 text3
text3 text4',' ',''),25)"

1
-------------------------
text1text2text3text3text4

1 record(s) selected.



Mehmet Baserdem wrote:
> Hi all,
>
> I am trying to remove all blank chars in middle of the string "text1
> text2 text3 text3 text4"
>
> with following stmt:
>
> VALUES(translate('text1 text2 text3 text3 text4' , ''
> ,' ' ))
>
> But it seems that the resulting string same with the input string.
>
> 1
> -------------------------------------
> text1 text2 text3 text3 text4
>
> 1 record(s) selected.
>
>
> Do you have any idea what is going on here?
>
> FYI:
>
> VALUES(translate('text1 text2 text3 text3 text4' , '*'
> ,' ' )) produces the
>
> right output
>
> 1
> -------------------------------------
> text1**text2***text3******text3*text4
>
> 1 record(s) selected.
>
> Regards,
>
> Mehmet Baserdem