10gR2 New Feature: Case Insensitive Searches

There are always a few topics in regards to writing SQL that always seem to come up more often than others. Querying data with case insensitivity is one of those topics. And Oracle has addressed this issue in many of their release. But in Oracle 10gR2 they have reached new levels. This article takes a dive into case insensitivity and how it is now handled in 10gR2. For the better!

Firstly for those that are new to case insensitivity I will give a very brief introduction here. Basically case insensitivity is the treating of upper case letters and lower case letters as equal when comparing or sorting them. So the comparison of the letters 'a' and 'A' in a case insensitive world would be equal. And if we had a table LETTERS(letter char(1)) and did the following query it would return the following two rows.

SQL> SELECT letter FROM letters WHERE letter = 'a';
LETTER
------
a
A

Up until the release of 10gR2 there have been many different approaches to accomplishing pattern matching such as using UPPER, NLS_UPPER, different NLS_SORT and NLS_COMP combinations such as GENERIC_BASELETTER for NLS_SORT, and you could even use Oracle's regular expression functions REGEXP%. But prior to 10gR2 writing a standard SQL statement that used a LIKE comparison operator and was able to take case insensitivity into consideration was non-existent. The only option was to use REGEXP_LIKE.

Before we get into how this is now solved in 10gR2 lets just take a quick look at the pieces of the solution and how they have worked in the past and how they currently work in 10gR2.

Table 1: NLS_COMP & NLS_SORT Visited

Examples in Oracle 10g

In prior versions of Oracle (pre 10gR2) if we had a table called NAMES and have the following entries in that table:

SQL> select name from names;
NAME
-------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON

And we wanted to perform a search on 'Johny Jacobson', under normal setting of case sensitive (NLS_SORT=BINARY) searching we would get the following. This is exactly how you would assume an equality to work.

SQL> alter session set NLS_SORT=BINARY;
Session altered.

SQL> select name from names where name = 'Johny Jacobson';
NAME
--------------------------------------------------------------
Johny Jacobson

But often times we don't necessarily know how someone might have entered data and thus we want to find all occurrences of a name, email address, or some other information. In this case we turn on case insensitivity. If we set NLS_SORT=BINARY_CI we would get the following results:

SQL>  alter session set NLS_COMP=ANSI;
Session altered.
SQL> alter session set NLS_SORT=BINARY_CI;
Session altered.

SQL> select name from names where name = 'Johny Jacobson';
NAME
--------------------------------------------------------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON

These last two examples used strict equality (where name = 'Johny Jacobson'). Often times, more often than not, we don't know much about the column contents and want to perform a search where we can supply only a portion of a name. So under normal settings of case sensitivity NLS_SORT=BINARY we may perform searches such where name LIKE '%John%', 'Johny%', '%ohn%' and so forth until we get a result set we are comfortable with. Using the normal setting of case sensitive (NLS_SORT=BINARY) searching we might do the following. Obviously if we are looking for all occurrences of case insensitivity this doesn't work.

SQL> alter session set NLS_SORT=BINARY;
Session altered.

SQL> select name from names where name like 'Johny%';
NAME
------------------------------------------------------------
Johny Jacobson

So we quickly, in prior versions of Oracle, tried to switch to case insensitivity by setting the NLS_SORT parameter. If we are still looking for the simple BINARY case insensitivity we might try the following. But as you can see in prior versions of Oracle we still do not get true case insensitivity!

SQL> alter session set NLS_COMP=ANSI;
Session altered.
SQL> alter session set NLS_SORT=BINARY_CI;
Session altered.

SQL> select name from names where name like 'Johny%';
NAME
------------------------------------------------------------
Johny Jacobson

This is where the new setting of NLS_COMP comes into play. Setting NLS_COMP to LINGUISTIC tells Oracle to perform strict case insensitivity operations and looks like the following two examples. As you can see there is complete case insensitivity for both equality and now LIKE operations.

SQL> alter session set NLS_SORT=BINARY_CI;
Session altered.
SQL> alter session set NLS_COMP=LINGUISTIC;
Session altered.

SQL>  select name from names where name = 'Johny Jacobson';
NAME
------------------------------------------------------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON

SQL> select name from names where name like 'Johny%';
NAME
------------------------------------------------------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON

There are literally dozens of ways in Oracle you can pattern match and find the information you need. The problem was, for case insensitivity you couldn't rely on strict case insensitivity in its purest form. You needed to hook in other functions or expressions. Now with Oracle 10gR2 you can set the NLS_COMP parameter and get true case insensitivity without having to change your application. Nice! Especially if you needed to port to another database vendor. Just so long as their case insensitivity is the same as Oracle's.