For those that may be interested.

(Source:
http://www.databasejournal.com/features/oracle/article.php/3494646)

...
Now, in Oracle 10g if we have a table called NAMES and have the
following entries in that table:

SQL> select name from names;
NAME
-------------
suzy smith
Suzy Smith
SUZY SMITH

If we wanted to perform a search on 'Suzy Smith', under normal setting
of case sensitive (NLS_SORT=BINARY) searching we would get the following:

SQL> select name from names where name = 'Suzy Smith';
NAME
------------
Suzy Smith

If we then wanted to turn on case insensitivity (NLS_SORT=BINARY_CI)
we would get the following results:

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

As you have noticed these examples use strict equality (where name =
'Suzy Smith'). Often times, more often than not, we want to perform a
search where we only know part of a name. So under normal settings of
case sensitivity NLS_SORT=BINARY when we perform a search for 'Suzy%'
we get the following:

SQL> select name from names where name like 'Suzy%';
NAME
------------
Suzy Smith

And when we switch to case insensitivity (NLS_SORT=BINARY_CI) we get
this result:

SQL> alter session set NLS_COMP=ANSI;
SQL> alter session set NLS_SORT=BINARY_CI;
SQL> select name from names where name like 'Suzy%';
NAME
------------
Suzy Smith

This is the exact same results and thus we gain nothing by using an
NLS_SORT setting for case insensitivity. As you can see case
insensitivity ONLY works for straight equality searches.


Tom