SEO

#1 (**permalink**) 02-27-2008, 07:11 AM

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

#2 (**permalink**) 02-27-2008, 07:12 AM

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

#3 (**permalink**) 02-27-2008, 07:12 AM

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

#4 (**permalink**) 02-27-2008, 07:12 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

translate function