Case insensitive search...

Well, I know this is not a new feature and this was exists from 10g but I just came across with this and wanted to record in my repository.

We know that there are functional indexes available for searching data in either UPPER or LOWER case searches.

But one can still achieve this case insensitive search by changing NLS parameters as demonstrated below.

Please note that this will not work for LIKE operator. 

SQL> show parameter nls_comp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY
SQL> show parameter nls_sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
SQL>

SQL> create table san_ci_test (user_name varchar2(30));

Table created.

SQL>
SQL> insert into san_ci_test values ('santhosh');

1 row created.

SQL> insert into san_ci_test values ('Santhosh');

1 row created.

SQL> insert into san_ci_test values ('SANTHOSH');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select *from san_ci_test where user_name = 'santhosh';

USER_NAME
------------------------------
santhosh

As you see there is only one row selected above.

Now change the NLS parameters and check the same query results.

SQL> alter session set nls_comp = ansi;

Session altered.

SQL> alter session set nls_sort = binary_ci; (ci - Case Insensitive)

Session altered.

SQL> select * from san_ci_test where user_name = 'santhosh';

USER_NAME
------------------------------
santhosh
Santhosh
SANTHOSH

SQL>

Yes these parameters can be set at database/instance level but I definitely wouldn't recommend that.