Oracle and
Case Insensitivity
In 10g release 2, we have the option
of setting case insensitivity to function with the REGEXP_LIKE
function.
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
parameters.
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:
execute
immediate 'alter session set NLS_COMP=LINGUISTIC';
execute
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:
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
)
;
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;
NAME
-------------
suzy smith
Suzy Smith
SUZY SMITH
SQL> alter session set NLS_COMP=ANSI;
SQL> alter session set NLS_SORT=BINARY_CI;
SQL> select name from names where name = 'Suzy Smith'
NAME
-------------
suzy smith
Suzy Smith
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');
NAME
-----------
Suzy Smith
SQL> select name from names where REGEXP_LIKE(name,'Suzy','i');
NAME
------------
suzy smith
Suzy Smith
SUZY SMITH
Follow the link for information on
Late Binding and Runtime Binding in PL/SQL.