Re: Case insensitivity in LIKE query - possible?

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%';
>
> The portion of the WHERE clause within the parentheses will allow the
> optimizer to use the index for a range scan, while not affecting the
> rows returned in the result set. This can dramatically improve the
> query performance.
>
> -p

Another solution is to create another column on the table (upper_ename) that stores the data in upper case. The user will see and update the normal ename column while the application does the searches against the upper_ename column. The application can populate the upper_ename column or if you are using Oracle 7 you can use a database trigger to maintain the field.

-- 
Scott Harvester

phone: (619) 658-4713            email: sharvester_at_qualcomm.com