Re: Searching case-insensitively

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