RE: SQL SELECT with Similar Terms

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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
> > >