Querying Oracle with Case Insensitivity

Before 10g R2, the only way to do case insensitive querying was by using the LOWER or UPPER functions, NLS_UPPER or NLS_SORT or NLS_COMP session parameters to put the data in a state in which it could be compared.

For example, take a scenario where a table has 100 entries, 60 have the category ‘cars’ and 40 have the category ‘CARS’

select Category, count(*) from SCHEMA.CATEGORY_TABLE
Group by Category;

CATEGORY    COUNT(*)

cars                  60

CARS               40  

To be able to return all the data, as one category the lower or upper function can be used:

select count(*) from SCHEMA.CATEGORY_TABLE
WHERE lower(category)='caRs';

the count returned in 100

To then speed up the data retrieval, a function based index can be added on the table (If the retrieval cost outweights the computation cost of the index:

CREATE INDEX SCHEMA.I_LOWER_CATEGORY ON SCHEMA.CATEGORY_TABLE
(LOWER("CATEGORY"));

Since 10g R2, the case insensitivity can be set at session level:

This is handled by the NLS_COMP session parameter. The sole purpose of this variable is to determine how the predicates in SQL statements will evaluate to each other when a comparison is required. The typical comparison operator is the WHERE clause but also includes such comparisons as ORDER BY, START WITH, HAVING, etc.

SQL> alter session set NLS_COMP=ANSI;

Session altered.

SQL> alter session set NLS_SORT=BINARY_CI;

Session altered.

SQL> select count(*) from schema.table where category = ‘CaRs’;

  COUNT(*)

———-

       100

this is discussed in full detail here

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: