How to make Oracle case insensitive

Question

Is there be a setting in Oracle 10g to consider data as case insensitive? I saw a solution here. However, that is done on a session. What I am looking for is a setting either on a schema or on a table to consider its data as case insensitive. If it is on a session then I will have to make the change on all the stored procedures.

Answer 1

There is no option to make a schema or table 'case insensitive'.

• You can do that on the session using the NLS_ parameters or do the same in the db init file where the whole instance is affected.
  However those searches are only case-insensitive for strict equality searches.

• If you need to use LIKE you then need to consider using REGEXP_LIKE.
  In the case of REGEXP_LIKE you don't need an NLS_SORT setting because there is an option for REGEXP_LIKE to have it consider things case insensitive.

Answer 2

Case sensitivity is fundamental to computing, for the simple reason that the ASCII value of 'yun' != the ASCII value of 'YUN'. So, when you say ...

consider data as case insensitive

... do you mean just for the purposes of search or for storage as well?

The problem with enforcing case on every search is this:

SQL> create table t23 (id number, name varchar2(20))
  2  /

Table created.

SQL> create unique index  t23_uk on t23(name)
  2  /

Index created.

SQL> insert into t23 values (1, 'SAM-I-AM')
  2  /

1 row created.

SQL> insert into t23 values (2, 'sam-i-am')
  2  /

1 row created.

SQL> select id, name, ascii(name) from t23
  2  /

        ID NAME                 ASCII(NAME)
---------- -------------------- -----------
         1 SAM-I-AM                      83
         2 sam-i-am                     115

SQL>

If case insensitive searches are enforced at the schema or table level how can we ever distinguish 'sam-I-am' from 'SAM-I-AM'?

It is - sort of - possible to enforce case-insensitivity for data storage, albeit at the individual column level:

SQL> delete from t23
  2  /

2 rows deleted.

SQL> drop index t23_uk
  2  /

Index dropped.

SQL> create unique index  t23_uk on t23(upper(name))
  2  /

Index created.

SQL> insert into t23 values (1, 'SAM-I-AM')
  2  /

1 row created.

SQL> insert into t23 values (2, 'sam-i-am')
  2  /
insert into t23 values (2, 'sam-i-am')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_UK) violated


SQL>