Case insensitive searching in Oracle

Question

There are 3 main ways to perform a case-insensitive search in Oracle without using full-text indexes.

Ultimately what method you choose is dependent on your individual circumstances; the main thing to remember is that to improve performance you must index correctly for case-insensitive searching.

1. Case your column and your string identically.

You can force all your data to be the same case by using UPPER() or LOWER():

select * from my_table where upper(column_1) = upper('my_string');

or

select * from my_table where lower(column_1) = lower('my_string');

If column_1 is not indexed on upper(column_1) or lower(column_1), as appropriate, this may force a full table scan. In order to avoid this you can create a function-based index.

create index my_index on my_table ( lower(column_1) );

If you're using LIKE then you have to concatenate a % around the string you're searching for.

select * from my_table where lower(column_1) LIKE lower('my_string') || '%';

This SQL Fiddle demonstrates what happens in all these queries. Note the Explain Plans, which indicate when an index is being used and when it isn't.

2. Use regular expressions.

From Oracle 10g onwards REGEXP_LIKE() is available. You can specify the _match_parameter_ 'i', in order to perform case-insensitive searching.

In order to use this as an equality operator you must specify the start and end of the string, which is denoted by the carat and the dollar sign.

select * from my_table where regexp_like(column_1, '^my_string$', 'i');

In order to perform the equivalent of LIKE, these can be removed.

select * from my_table where regexp_like(column_1, 'my_string', 'i');

Be careful with this as your string may contain characters that will be interpreted differently by the regular expression engine.

This SQL Fiddle shows you the same example output except using REGEXP_LIKE().

3. Change it at the session level.

The NLS_SORT parameter governs the collation sequence for ordering and the various comparison operators, including = and LIKE. You can specify a binary, case-insensitive, sort by altering the session. This will mean that every query performed in that session will perform case-insensitive parameters.

alter session set nls_sort=BINARY_CI

There's plenty of additional information around linguistic sorting and string searching if you want to specify a different language, or do an accent-insensitive search using BINARY_AI.

You will also need to change the NLS_COMP parameter; to quote:

The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.

The default value of NLS_COMP is BINARY; but, LINGUISTIC specifies that Oracle should pay attention to the value of NLS_SORT:

Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

So, once again, you need to alter the session

alter session set nls_comp=LINGUISTIC

As noted in the documentation you may want to create a linguistic index to improve performance

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

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:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKE but it seems to work with good old = as well.

---

In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitive search, is to just UPPER() both the column and the search expression.

maybe you can try using

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

From Oracle 12c R2 you could use COLLATE operator:

The COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.

The COLLATE operator takes one argument, collation_name, for which you can specify a named collation or pseudo-collation. If the collation name contains a space, then you must enclose the name in double quotation marks.

Demo:

CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab1(i, name) VALUES (1, 'John');
INSERT INTO tab1(i, name) VALUES (2, 'Joe');
INSERT INTO tab1(i, name) VALUES (3, 'Billy'); 
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = 'jOHN' ;
-- no rows selected

SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/

SELECT /*csv*/ *
FROM tab1 
WHERE name LIKE 'j%';
-- no rows selected

SELECT /*csv*/ *
FROM tab1 
WHERE name COLLATE BINARY_CI LIKE 'j%';
/*
"I","NAME"
1,"John"
2,"Joe"
*/

db<>fiddle demo