Re: PROBLEM WITH LIKE OPERATOR IN SELECT WHERE CLAUSE

You could change the field type to varchar2 or use a function based index. Thus avoiding the full table scan.
Jim

"shamai markel" <shamaim_at_amdocs.com> wrote in message news:991812446.968712_at_sointnews.amdocs.com...
> Already did, however since the field has an index on it, it causes oracle
> not to use the index and thus it hinders performance. Trying to force
 oracle
> to use the index by providing it with a hint, didn't help.
> thanks very much for answering :)
>
>
> "Randall Roberts" <randall_at_filer.org.nospam> wrote in message
> news:3b0dbc3b_3_at_news.pcmagic.net...
> > Try
> >
> > SELECT * FROM table WHERE RTRIM(field, ' ') LIKE 'A%C'
> >
> > Best!
> >
> > Randall
> >
> >
> > shamai markel <shamaim_at_amdocs.com> wrote in message
> > news:990615107.590810_at_sointnews.amdocs.com...
> > > Hi,
> > > 1. In difference from '=' operator, LIKE opearator does not ignore
 trailing
> > > blanks in CHAR type fields.
> > > 2. This means that for a CHAR(5) field containing 'ABC ', the
 following
> > > SELECT statement will return no lines :
> > > SELECT * FROM table WHERE field LIKE 'A%C', whereas following SELECT
> > > statement will return the line :
> > > SELECT * FROM table WHERE field = 'ABC'.
> > > 3. Does anyone have a bypass to this problem that doesn't require to
 pad
> > > blanks to the searched string and that doesn't inluence performance
 ?
> > > thanks very much
> > > shamai
> > >
> > >
> >
> >
>
>
Received on Thu Jun 07 2001 - 14:23:38 CEST