Making Oracle Case insensitive

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 ONempp(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:

Disclaimer

This blog contains things about technology that I jot about, keep for my records. Some articles in this blog are not owned by me. They are for my reference only. In older posts the original links may not be there, its not intentional. Thanks for understanding.