The Oracle (tm) Users' Co-Operative FAQ

How do you write a query that ignores the effects of upper and lower case ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 22, 2001

Oracle version(s): 8.0+

How do you write a query that ignores the effects of upper and lower case ?

Back to index of questions


Some databases have an "ignore case" flag that can be set for the entire database. Oracle does not, and thus case-insensitive queries have long caused problems, not with coding them, but with their performance (since indexes are typically not used to determine the result).

Its relatively straightforward to create a case-insensitive query:

SQL> select *
  2  from EMP
  3  where upper(ENAME) = upper(:b1)

but of course (by default) the "UPPER(ENAME)" cannot take advantage of an index that may have been defined on the ENAME column.

Enter 8i, where the concept of a function-based index is now possible. Before you rush off and try to create them, take note of the following:

and then its just a case of creating the index in the conventional way:

create index UPPER_ENAME_IX on ENAME ( UPPER(ENAME) ) ;

Note that this index will NOT be used for case-SENSITIVE queries. You could always have two indexes, one on ENAME and one on UPPER(ENAME), but it would probably be more efficient to just have the function-based one and code:

SQL> select *
  2  from EMP
  3  where upper(ENAME) = upper(:b1)
  4  and ENAME = :b1

for the times where you do not want case-insenstivity.


Further reading: Querying the dictionary for function-based indexes


Back to top

Back to index of questions