Case insensitive searching in Oracle

Question

The default behavior of LIKE and the other comparison operators, =, etc is case-sensitive.

Is it possible to make them case-insensitive?

Answer 1

In Oracle 10gR2, you can fine-tune the behavior of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

Query:

SQL> SET HEADING OFF

SQL> SELECT *

  2  FROM NLS_SESSION_PARAMETERS

  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT

BINARY

NLS_COMP

BINARY

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

  2  FROM DUAL;

         0

SQL>

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>

SQL> SELECT *

  2  FROM NLS_SESSION_PARAMETERS

  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT

BINARY_CI

NLS_COMP

LINGUISTIC

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

  2  FROM DUAL;

         1

You can also create case insensitive indexes like this:

Query:

create index

   nlsci1_gen_person

on

   MY_PERSON

   (NLSSORT

      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')

   );