translate function

[Mehmet Baserdem]

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

[4.spam]

Hello.
Try this:
--
VALUES(replace('text1 text2 text3 text3 text4' , ' ' ,''
))
--

Sincerely,
Mark B.

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' , ''
,' ' ))

[Peri]

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

[Mehmet Baserdem]

Thanks Mark and Peri,

I was trying to trim the intermediate space chars. It seems that
"Replace: function is getting the job done.

Regards,

Mehmet Baserdem