Re: Case insensitive searches

   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.

HTH,

-- 
Stéphane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Author, SQL Success
<http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>,
The Art of SQL
<http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>,
Refactoring SQL Applications
<http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/>



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


> RHEL 5.10 / Oracle 11.2.0.3

>

> 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 11.2.0.4 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

>



--
http://www.freelists.org/webpage/oracle-l