RE: SQL SELECT with Similar Terms

I wondered too about the preference against translate() function - thought it might be thought that the rtrim() function would process fewer bytes - translate() probably has to go through the entire value - rtrim() - depending how it is written - COULD test fewer bytes, depending on where the last non-trimmed character is.

Also, it seems that the version using rtrim() should perhaps use trim() instead - nothing says there are no leading blanks. That would bring it closer to testing all the bytes.

Personally I think the difference is minuscule - use whichever makes sense to you syntactically. But a test WOULD be interesting - especially over 1 million records.

Guess the official PERL motto applies here TMTOWTDI - google that, my friends!

Looking forward to driving topless in the rain to Illinois!
Vern

At 11:54 AM 8/17/2007, you wrote:

Trim is the same thing as strip.
"The STRIP function is identical to the TRIM scalar function. For more information, see "TRIM" on page
311."

Be interesting to know which has better performance:
1) Trim + translate
2) Trim + Rtrim

The translate method has the advantage of allowing multiple punctuations to be handled.

Charles


> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
> Sent: Friday, August 17, 2007 12:05 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL SELECT with Similar Terms
>
> Wow...two ways to do something I didn't think could be done.
> Is this list great or what?
>
> On 8/17/07, BirgittaHauser <Hauser@xxxxxxxxxxxxxxx> wrote:
> > Hi,
> >
> > I prefere the scalar function TRIM:
> >
> > select trim(Both ':' from RTrim(MyField)), count(*) from
> mytable group
> > by Trim(Both ':' from RTrim(MyField))
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >