Making Oracle Case insensitive

Well, this article is the fruit of thoughts on which how you can make Oracle's output case insensitive. Of course, the simplest way is to use the UPPER() function to change the case of either data stored or data being checked. For Example:

SELECT * 

FROM emp

WHERE UPPER(ename) = 'RAVI';

Function Based Index 

But by following this method, there is one problem. If the column ename is having an index, it will no longer be used. But starting from Oracle 8i, there is a concept called function-based index. Before getting to use function-based indexes, the following criteria must be met with:

• You must have the system privilege query rewrite to create function based indexes on tables in your own schema.
• You must have the system privilege global query rewrite to create function based indexes on tables in other schema's.
• For the optimizer to use function based indexes, the following session or system variables must be set:
  QUERY_REWRITE_ENABLED=TRUE
  QUERY_REWRITE_INTEGRITY=TRUSTED
• You must be using the Cost Based Optimizer (which means analyzing your tables/indexes)

And then it’s just a case of creating the index in the conventional way:

create index UPPER_ENAME_IX on ENAME ( UPPER(ENAME) ) ;

Note that this index will NOT be used for case-SENSITIVE queries. You could always have two indexes, one on ENAME and one on UPPER(ENAME), but it would probably be more efficient to just have the function-based one and code.

REGEXP_LIKE

With the release of Oracle version 10g, the regular expressions came to help for solving the problem. The following query can be executed to get case insensitive output:

SELECT * 

FROM emp

WHERE REGEXP_LIKE(ename,'ravi','i');

The output is:

ENAME               
--------------------
rAvi                
RAVI                
Ravi    

NLSSORT Function

There is one more way by which we can achieve this output. We can create a function-based index using nlssort() function. Like:

CREATE INDEX empp_idx ON
empp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));

The above approach will not invalidate the index, and will hold a good candidate for using case-insensitive queries.

There is one more approach where it is no required  to change the queries. We just need to update two oracle system parameters, NLS_COMP and NLS_SORT. The following example will suffice:

NLS_COMP, NLS_SORT

alter session set NLS_COMP=ANSI;

alter session set NLS_SORT=BINARY_CI;

select ename from empp where ename = 'ravi';

ENAME               
--------------------
rAvi                
RAVI                
Ravi      

This approach will be far more efficient because of two reasons:

1. Indexes will be used if it exists on the column.

2. No development (rework) is required to make queries insensitive.

Click here to read How to Change Oracle back to Case Sensitive once again