Re: Case insensitivity in LIKE query - possible?

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

Paul Zola                                                 Technical Specialist
World-Wide Technical Support                                 Development Tools
==============================================================================
Computers possess the truly profound stupidity of the inanimate. - B. Sterling
Disclaimer: 	Opinions and statements are mine, and do not necessarily
		reflect the opinions of Oracle Corporation.