Re: Searching case-insensitively

From: Paul Zola <pzola_at_us.oracle.com>
Date: 1996/07/15
Message-ID: <4scgq6$jlu_at_inet-nntp-gw-1.us.oracle.com>#1/1

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:
}
} 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?
 [snip]
} 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?

There's the following way to perform a case-insensitive query, which will give you an efficient case-insensitive search:

   SQL> select FIRST_NAME,LAST_NAME

        from patient
        where (
                LAST_NAME like 'AN%' or
                LAST_NAME like 'An%' or
                LAST_NAME like 'aN%' or
                LAST_NAME like 'an%' )
            and upper(LAST_NAME) like 'ANDERSON%';

The portion of the WHERE clause within the parentheses will allow the optimizer to use the index for a range scan, while not affecting the rows returned in the result set. This can dramatically improve the query performance.

---

Paul Zola Technical Specialist World-Wide Technical Support

---

GCS H--- s:++ g++ au+ !a w+ v++ C+++ UAV++$ UUOC+++$ UHS++++$ P+>++ E-- N++ n+

    W+(++)$ M+ V- po- Y+ !5 !j R- G? !tv b++(+++) !D B-- e++ u** h f-->+ r*

---

Disclaimer: 	Opinions and statements are mine, and do not necessarily
		reflect the opinions of Oracle Corporation.

Received on Mon Jul 15 1996 - 00:00:00 CEST