Case and Accent-Insensitive String Comparisons in Oracle Using Collations

Sometimes, when comparing strings in Oracle, we want to ignore whether the text is in uppercase or lowercase, or whether it includes accent marks. For that, Oracle provides a feature called COLLATE, which can be applied at the table, session, or query level. In this article, we'll focus on query-level collations.

Practical Example:

Let’s say you want to search for a person named José, but in the database the name is stored as Jose, or vice versa. The default comparison would look like this:

SELECT * FROM DUAL WHERE 'JOSÉ' = 'JOSE';

Result: no rows selected.

Why? Because, by default, Oracle treats 'JOSÉ' and 'JOSE' as different strings. However, if we apply a specific collation, Oracle can ignore accent differences during the comparison.

Take a look at the following examples:

SELECT * FROM DUAL WHERE 'JOSÉ' COLLATE BINARY_AI = 'JOSE'; 
SELECT * FROM DUAL WHERE 'JOSÉ' COLLATE BINARY_AI = 'Jose';

If we run these queries, each one will return a row. Why is that?

When we use COLLATE BINARY_CI or BINARY_AI, we’re telling Oracle to compare strings using the following rules:

• _CI (Case Insensitive): Ignores differences in upper/lower case, but respects accents.

• _AI (Accent Insensitive): Ignores both accents and letter case.

Simple and effective!

You can also apply collation at the table or column level, so that every time you query those fields, Oracle automatically ignores case and accent differences.

Sources:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/linguistic-sorting-and-matching.html#GUID-68633595-DC95-4393-A6B8-146AD05F5FDF

https://binovarghese.com/notes/2022/10/column-level-collation-and-case-insensitive-database-in-oracle/