| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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 OracleThis is an on Oracle article. The most current articles of this series can be found here.
|