Re: Case-INSENSITIVE searches?? Possible??

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"