Content
This document describes how to configure Oracle NLS to support case insensitive searches.
Oracle databases are by default configured as case sensitive. Version 10gR2 added a new feature to its NLS (National Language Support) that allows to do case-insensitive sorts and comparisons altering the session settings.
This can be done by changing the value of two NLS parameters, NLS_SORT and NLS_COMP.
The NLS_SORT parameter governs the collation sequence for ordering and the various comparison operators, including ‘=’ and ‘LIKE’.
You can specify a binary, case-insensitive, sort by altering the session, for instance:
alter session set nls_sort=BINARY_CI;
This will mean that every query performed in that session will perform case-insensitive searches.
Further information about linguistic sorting and string searching can be found here. For instance, you can specify a different language, or you can use BINARY_AI to configure an accent-insensitive search.
The NLS_COMP parameter decides the exact operators and query clauses that are going to follow the NLS_SORT parameter.
The default value of NLS_COMP is BINARY, this means that queries are not going to follow the NLS_SORT configuration. In order for Oracle to pay attention to the value of NLS_SORT, we must change the value of NLS_COMP to LINGUISTIC:
alter session set nls_comp=LINGUISTIC;
Since these parameters can be modified at session level we can configure this for any Oracle JDBC data source in Denodo Virtual DataPort. You can do this by providing the ALTER SESSION statement in the initial SQL sentences option under the data source configuration:
After that, once you run any query or view from that data source, the comparison will be based on the newly configured NLS_SORT and NLS_COMP.
All three names were found, even though we have specified incorrect cases for all three comparisons.
References
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.