Enable case insensitive search in Oracle

Problem

You have loaded a significant amount of data that is in mixed case. You want to query as if it were all lowercase (or all uppercase).

Solution

If you are using Oracle Database 10g or higher, you can use the ALTER SESSION statement enable case-insensitive searching with the >, <, and = comparison operators. To enable case-insensitive searching, alter the NLS_SORT and NLS_COMP parameters:

alter session set nls_sort=binary_ci; alter session set nls_comp=linguistic;

These settings allow case-insensitive searches. You can run the following query to select all values of Boris, regardless of case:

select * from emp where name ='BORIS'; NAME -------------------- Boris BORIS boris

You could also use an UPPER or LOWER SQL function to perform case insensitive searches:

select * from emp where upper(name) ='BORIS'; NAME -------------------- Boris BORIS boris

Note: Oracle will ignore an index on a column that has a SQL function applied to it. To get around this, you can create a function-based index.

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.