Re: How to do Case Insensitive Search using LIKE operator

Shekhar Kirani wrote:

> In ORACLE is it possible to make a case insensitive search
> on columns where columns contain case sensitive data?
>
> For example, suppose NAMES column contain 'JDoe', 'SKirani' and so on.
> How do I make a SQL like
> SELECT NAMES FROM PERSON WHERE NAMES LIKE %sKi%
> return the 'SKirani' row?
One method is to put an equalizing function on both sides of the where condition

i.e. where upper(names) like upper ('%sKi%')

but the use of upper on names would invalidate any index use (which the leading % to like would have done anyway.

The real answer is to store the data in a corrected form in a second search column (i.e. put Name however typed into name but store UPPER(Name) into another col e.g. search_name) and to then use this column for the search. Received on Fri Oct 25 1996 - 00:00:00 CEST