Re: Case insensitivity in LIKE query - possible?

In article <4k8tmc$qn0_at_inet-nntp-gw-1.us.oracle.com>, pzola_at_us.oracle.com (Paul Zola ) wrote:
>In <4jf9vu$rr8_at_news.missouri.edu> pixel_at_rhino.coe.missouri.edu (James P.
> Cooper) writes:
>
>
>} Hello,
>}
>} I'm using Oracle 7.1 on an SGI, and would like to perform some case
>} insensitive queries using LIKE.
>[snip]
>
>There are two classic ways of doing this under Oracle (I can't
>speak for other database platforms). The first is:
>
> SQL> select ename from emp where upper(ename) like 'SMIT%';
>
>The problem with this is that the usage of a function (upper()) in
>the WHERE clause will inactivate the usage of indexes, and will
>force a full table scan. This can be a major problem for queries
>against large tables.
>
>This leads to the second classic solution, which is somewhat more
>complex. This is:
>
> SQL> select ename from emp
> where (
> ename like 'SM%' or
> ename like 'Sm%' or
> ename like 'sM%' or
> ename like 'sm%' )
> and upper(ename) like 'SMIT%';

Fancy...
Why not just :
UPDATE MY_TABLE SET ENAME=UPPER(ENAME) ??? once and forever... forever???
:)
Dave Received on Mon Apr 08 1996 - 00:00:00 CEST