LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon