By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,632 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,632 IT Pros & Developers. It's quick & easy.

translate function

P: n/a
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

May 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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' , ''
,' ' ))


May 31 '06 #2

P: n/a
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


May 31 '06 #3

P: n/a
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

May 31 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.