Re: Case insensitive searches

From: Stéphane Faroult <>
Date: Tue, 18 Mar 2014 08:36:58 +0100
Message-ID: <>


   The simple, obvious solution is to turn every index on a varchar2 column into a function-based index on upper(column). If the column isn't indexed, upper(col) = upper('input') won't hurt you much. An important point that people tend to overlook is that it may force you into a review of your constraints; case-insensitive searches may also mean that you want case-insensitive uniqueness, which cannot be enforced by a declared constraint (you cannot say that "unique (upper(surname))" but only through a unique function-based index.

   Storing only a standard case inside the database is OK with Y/N flags or some codes (country codes, IATA codes and the like), but isn't, for my taste, a good enough solution with names (people surnames and company names) for which case is sometimes peculiar. A function such as initcap() may help you beautify some data, but is useless with McLeods, people called ffrench (a double initial lower case ff is less common than a Gaelic surname), von Richthoffen, van Dijk or de La Rochefoucauld for instance. You may choose to irritate a small percentage of users (who must be accustomed to see their names misspelt by computers by now) but I prefer storing data as people want to see it and turn to FBIs for case-insensitivity.


Stéphane Faroult
RoughSea Ltd <>
Konagora <>
RoughSea Channel on Youtube <>
Author, SQL Success
The Art of SQL
Refactoring SQL Applications

On 03/17/2014 11:02 PM, Ryan January wrote:

> RHEL 5.10 / Oracle
> This seems to be one of those classic situations that everyone runs
> across at one time or another. We've been approached to assist in
> making an existing in-house application's queries case insensitive.
> Many things were discussed but the first option we're testing is
> NLS_SORT and NLS_COMP changes. The settings remain default at the
> database level. We've configured a login trigger to set NLS_SORT to
> BINARY_CI and NLS_COMP to LINGUISTIC for sessions initiated by a
> particular user. Less than a full day into testing we've already
> consistently run into ORA 600's via Bug #15936924 (MOS 15936924.8).
> While it's sad to admit, I really wasn't surprised. I expected to run
> into issues, but not this soon in, and not this severe.
> Where it goes from unfortunate to infuriating is that the planned fix
> is to be included in 12.2 with no work around listed. The doc also
> mentions as being affected so there's no end in sight.
> In light of this I think we need to take a step back and see how
> others have approached these situations.
> Having no plans to attempt a resolution on any announced time frame
> leads me to believe Oracle simply doesn't have many users covering
> this code path. Are these non-standard NLS settings really that out
> of the norm? Has anyone else run across similar issues in the past?
> Did you ever find a work around?
> If you haven't run into this issue, how are you handling case
> insensitivity mandates? Do you handle this in the app stack outside
> the database? Do you change the data model and only store a standard
> case inside the database? Do nothing outside of avert your eyes when
> you see sql containing upper() on both sides of a predicate?
> Any insight you can provide would be appreciated.
> Thanks,
> Ryan
Received on Tue Mar 18 2014 - 08:36:58 CET

Original text of this message