Oracle Case Insensitive search

Example:

create table san_ci (name varchar2(20));

insert into san_ci values ('John smith');
insert into san_ci values ('John Smith');
insert into san_ci values ('John SMITH');
insert into san_ci values ('John SMITH');
insert into san_ci values ('JOHN smITH');
commit;


select name
  from san_ci
 where name like '%mit%';


NAME
John smith
John Smith

Using Regular Expression Function:

select name
  from san_ci
 where regexp_like (name,'mit','i');
NAME
John smith
John Smith
John SMITH
John SMITH
JOHN smITH

Using 10g NLS parameters:


alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

select name
  from san_ci
 where name like '%mit%'; --notice the results for the same query above without setting these NLS parameters.
NAME

John smith
John Smith
John SMITH
John SMITH
JOHN smITH

You can create an NLS_SORT functional index on the column if the table that is being used in search criteria is huge:

create index san_ci_idx1 on san_ci (NLSSORT (name, 'NLS_SORT=BINARY_CI') );

Creating the above index alone will not help the query to search the data in case insensitive. You have to set the session variables for case insensitive searches.