Re: using "WHERE" without case sensitivity
Date: 1998/01/14
Message-ID: <69ifc3$2ee$1_at_news.ses.cio.eds.com>#1/1
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
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
( name = 'Joe' or
name = 'joe')
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?
Received on Wed Jan 14 1998 - 00:00:00 CET