Re: using "WHERE" without case sensitivity

Bert Scalzo wrote:
>
> Yes. Do not add another column. Index column as normal. Then adjust all
> queries as follows:
>
> using just first character:
>
> select * from table_name where name like 'joe' and
> ( name = 'Joe' or
> name = 'joe')
>
> The exlplain plan will show index being used due to the like, and the other
> two conditions resolved against index data.
>
> You can expand this for 2 characters by making more or conditions within the
> parens. I have found little difference between using more than 2 positions
> (i.e. 4 or conditions).
>
> Bert Scalzo
> EDS
>
> Steven Schultz wrote in message <34BBDC97.415B_at_home.com>...
> >I want to be able to search for text within fields and select records
> >even if the case is not the same (i.e. "Joe" = "joe"). The only two
> >methods I have come up with is to add fields to the tables where the
> >data to be searched on has been made into all upper or all lower case
> >and convert the text being searched for into the same case or use the
> >upper or lower functions on both the fields and the text to be searched.
> >Neither option is a good one. What I would like is to be able to create
> >an index which converts all text to one case. Is there any capabilities
> >in Oracle which assists with this problem?

I don't think I made my question was completely clear. The varchar2 value that I want to match in Oracle can be any combination of upper and lower case, i.e., jOe, joE, JoE, etc. Received on Fri Jan 16 1998 - 00:00:00 CET