Re: Case-INSENSITIVE searches?? Possible??

From: Steve Bell <swayne.bell_at_sympatico.ca>
Date: Wed, 04 Apr 2001 13:45:40 GMT
Message-ID: <3ACB22C2.14DB8526@sympatico.ca>

Hi Connor,
Yeah.. I meant

where UPPER(ename) like 'BLAKE' for example..in other words, use UPPER or LOWER (but not both) on the column itself.
Steve

Connor McDonald wrote:

> Steve Bell wrote:
> >
> > Hi John,
> > No, that would be redundant..you can choose whether to use UPPER or LOWER but
> > you only need one or the other.
> > There are other options, as another poster to this thread has suggested ..I was
> > merely answering your specific question.
> >
> > Best regards John,
> > Steve
> >
> > John Peterson wrote:
> >
> > > Hello, Steve!
> > >
> > > Would you also want to process the ename field with the UPPER function as
> > > well (for consistency's sake)?
> > >
> > > Also, is there any other way to do a case-insensitive search? I've got a
> > > dynamic query that has quite a few strings that are being searched, and the
> > > DML is "littered" with a ton of calls to LOWER. I'm sure that's not the
> > > most optimal, but...
> > >
> > > "Steve Bell" <swayne.bell_at_sympatico.ca> wrote in message
> > > news:3AC33724.877A401_at_sympatico.ca...
> > > > Hi Alex,
> > > >
> > > > Another poster has answered that you can use UPPER or LOWER row functions
> > > > to do this. I'll elaborate a bit with an example:
> > > >
> > > > SQL> select ename from emp
> > > > 2 where ename like UPPER('bla%');
> > > >
> > > > ENAME
> > > > --------------------
> > > > BLAKE
> > > > BLAKESON
> > > > BLAKESMITH
> > > > BLAKESONSMITH
> > > >
> > > > SQL>
> > > >
> > > > Hope this helps,
> > > > Steve
> > > >
> > > > Alex Skolnic wrote:
> > > >
> > > > > How can I perform a case-insensitive search?
> > > > >
> > > > > I trying to perform a search on any portion of a description field
> > > > > that can be entered in Mixed case. My SQL statement is using the LIKE
> > > > > '%search%' statement, but it won't return matches that don't have the
> > > > > same case.
> > > > >
> > > > > Any ideas?
> > > >

>

> I dunno about that...

>

> select *
> from emp
> where ename like UPPER(..)

>

> won't get any emps with a lower case name, similarly

>

> where ename like LOWER(..)

>

> won't get any emps with a upper case name

>

> You'll upper(ename) = upper(...), or lower(ename) = lower(..) to get the
> lot

>

> hth
> connor

>

> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)

>
> "Some days you're the pigeon, some days you're the statue"
Received on Wed Apr 04 2001 - 08:45:40 CDT