Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Case insensitive search and index

user613483Jul 2 2009 — edited Jul 3 2009
I have to execute a case insentitive search.
I created this index on a not null field:


create index indx_prova on
table (nlssort(campo, 'NLS_SORT=BINARY_CI'));


The select is:

select * from tabella where campo like 'A storage%'

This select should retrive 5 records:
A storage ring for crystalline beam studies
a storage ring for crystalline beam studies
A Storage Ring for Crystalline Beam Studies
A storage ring for crystalline beam studies
A storage ring for crystalline beam studies

Instead I got only 3 records:

A storage ring for crystalline beam studies
A storage ring for crystalline beam studies
A storage ring for crystalline beam studies

So The query isn't case insensitive.

I can't set nls_sort=BINARY_CI and nls_comp=LINGUISTIC at level session.

Is there a solution.
Am I doing something wrog?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2009
Added on Jul 2 2009
11 comments
8,702 views