Re: Searching case-insensitively

In <31E0C1A7.6113_at_mpimg-berlin-dahlem.mpg.de>, Simon Mercer <mercer_at_mpimg-berlin-dahlem.mpg.de> writes:
>Simon Mercer <mercer_at_mpimg-berlin-dahlem.mpg.de> wrote:
>
>>I have a table with two fields, 'ID' and 'name' with maybe 2 million
>>records, and I want to conduct a search on the contents of the 'name'
>>field. This field can contain any alphanumeric string, often with a
>>mixture of upper and lower case characters. What is the best way to
>>search this table case-insensitively?
>
>Well, so far I know that if I use functions I can't use indices, and
>that there is no other way to switch to a default case-insensitive
>searching behaviour. Forcing text entry in uppercase is not an option
>because case has meaning in these text strings.
>
>So there's no way to do this (unless someone knows different)....
>
>Surely this is a MASSIVE shortcoming in Oracle, or am I the first person
>in the world who needs an efficient case-insensitive search?
>
>Simon
>
>--
>Dr. Simon Mercer
>The Reference Library Database
>+49 30 8413 1395 (fax)
>mailto:mercer_at_mpimg-berlin-dahlem.mpg.de

Here's a work-around you may consider,

Have a column that is the upper() value of the name field. i.e.

alter table xx add name_upper char(x);
update xxx set name_upper=upper(name);
create index xxx_idx on xxx(upper_name);

Do your searches on the upper_name column and leave the name column as it should be.

..where upper_name = upper(name_variable)

I know this means 2000000*upper_name size more space required, but thats the best I can do !!

Bye,
Steve

| any similarity 'tween my opinions and that |
|  of my employers are purely hypothetical   |
|     and should give no cause for alarm     |
 -----------------------------------