Case-insensitive comparisons in Oracle
Options for case-insensitive comparisons in Oracle SQL: normalising with UPPER or LOWER, regular expressions, linguistic sort settings and function-based indexes.
Oracle compares strings case-sensitively by default. The approaches below make a comparison case-insensitive: normalise both sides, use a regular expression, change the session linguistic settings, or index the expression.
Normalise both sides
Apply the same function to the column and to the value:
SELECT * FROM my_table WHERE UPPER(column_1) = UPPER('my_string');
SELECT * FROM my_table WHERE LOWER(column_1) = LOWER('my_string');
For a prefix search with LIKE:
SELECT * FROM my_table WHERE LOWER(column_1) LIKE LOWER('my_string') || '%';
To keep such queries fast, index the expression. The index is used only when the query uses the same expression:
CREATE INDEX my_index ON my_table (LOWER(column_1));
Use regular expressions
The third argument 'i' makes REGEXP_LIKE case-insensitive:
SELECT * FROM my_table WHERE REGEXP_LIKE(column_1, '^my_string$', 'i');
SELECT * FROM my_table WHERE REGEXP_LIKE(column_1, 'my_string', 'i');
The first form matches the whole value; the second matches the text anywhere in the value.
Change the session settings
Linguistic comparison and sorting can be set for the current session only:
ALTER SESSION SET NLS_SORT = BINARY_CI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;
Index the linguistic sort
With a linguistic index, the query uses the same NLSSORT expression as the index:
CREATE INDEX my_linguistic_index ON my_table (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
For this index to be used, the queries must run with the same NLS_SORT value and with NLS_COMP=LINGUISTIC.