translate function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mehmet Baserdem - 30 May 2006 20:00 GMT

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

Reply to this Message

4.spam@mail.ru - 31 May 2006 07:04 GMT

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"
[quoted text clipped - 3 lines]
> VALUES(translate('text1 text2 text3 text3 text4' , ''
> ,' ' ))

Reply to this Message

Peri - 31 May 2006 10:57 GMT

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.

> Hi all,
>
[quoted text clipped - 32 lines]
>
> Mehmet Baserdem

Reply to this Message

Mehmet Baserdem - 31 May 2006 15:35 GMT

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

Reply to this Message