René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

June 24, 2005: On case insensitive queries in 10g

Oracle 10g allows to select strings in a case insensitive manner by adding _ci to nls_sort. In order for Oracle to respect this setting, nls_comp must be set to ansi (the default being binary). 
alter session set nls_comp=ansi;
Now, I create a table... 
create table ci_test (
  num  varchar2(10),
  item varchar2(10)
);
... and fill it with some mixed case strings: 
insert into ci_test values ('one'  , 'screen');
insert into ci_test values ('TWO'  , 'flower');
insert into ci_test values ('thRee', 'piano');
insert into ci_test values ('FouR' , 'bulp'  );
insert into ci_test values ('fiVE' , 'fridge');
I explicitly require to select case sensitively: 
alter session set nls_sort=binary;
The select statement: 
select item from ci_test where num in ('one','four');
Here's the output: 
ITEM
----------
screen
By appending _ci to the nls_sort value (and having nls_comp=ansi), the select statement will search case insensitively: 
alter session set nls_sort=binary_ci;
The same statement: 
select item from ci_test where num in ('one','four');
The new output: 
ITEM
----------
screen
bulp

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.