Collation Modifiers in Databricks

Make case-insensitive or accent-insensitive queries easier and improve performance.

What Is Collation?

A collation defines how strings are compared and sorted. You can set it when creating a table (e.g., CREATE TABLE) or use it in WHERE or ORDER BY clauses (e.g., ORDER BY mycol COLLATE “UNICODE_CI”).

If you are not yet a member of Medium, you can access for free via a friend’s link.

-- Specify during creation of table

CREATE TABLE my_table (
  id INT,
  name STRING COLLATE "UNICODE_CI_AI"
);

-- You can also specify collation on the fly

SELECT name
FROM my_table
WHERE name COLLATE "UNICODE_CI" = 'john';

Key Modifiers:

CS: Case-Sensitive (default). ‘A’ ≠ ‘a’
CI: Case-Insensitive. ‘A’ = ‘a’
AS: Accent-Sensitive (default). ‘e’ ≠ ‘é’
AI: Accent-Insensitive. ‘e’ = ‘é’
RTRIM: Ignores trailing spaces. ‘Hello’ = ‘Hello ‘

You can combine modifiers (e.g., CI_AI_RTRIM).

WHERE Clause Examples:

Case-Insensitive Match