In 10g release 2, we have the option
of setting case insensitivity to function with the LIKE operator.
Here we show how to take advantage of that powerful new feature.
Setting the Oracle Parameters for
case insensitive searching
We need to set
NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI in order to
use 10gR2 case insensitivity. Since these are session modifiable,
it is not as simple as setting them in the initialization
We can set them in the initialization parameters but
they then only affect the server and not the client side. The
following PL/SQL was used for a search screen:
immediate 'alter session set NLS_COMP=LINGUISTIC';
immediate 'alter session set NLS_SORT=BINARY_CI';
Tuning for case insensitive queries:
Depending on the size of the table and
type of queries, we may want to build linguistic indexes on the
columns to allow case insensitive queries. This is the sample code
for an index:
This is a great example of using case insensitive
data inside Oracle and creating a case insensitive index to allow
queries to me made in initcap, upper or lower, all without
invalidating the index.
SQL> select name from names;
SQL> alter session set NLS_COMP=ANSI;
SQL> alter session set NLS_SORT=BINARY_CI;
SQL> select name from names where name = 'Suzy Smith'
So for our example of searching for 'Suzy%' and having
an NLS_SORT for case sensitive searching (NLS_SORT=BINARY)
we could issue the following types of SELECT statements and
get the following results:
SQL> select name from names where REGEXP_LIKE(name,'Suzy');
SQL> select name from names where REGEXP_LIKE(name,'Suzy','i');
Follow the link for information on
Late Binding and Runtime Binding in PL/SQL.