SQL COLLATE

What is the SQL COLLATE keyword?

COLLATE overrides the default collation so text comparison and sorting follow the specified locale-specific rules.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COLLATE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake. Exact collation names differ by engine.

SQL COLLATE Full Explanation

COLLATE lets you dictate how strings are compared and ordered at query time or column definition time. A collation defines character set, case sensitivity (CS/CI), accent sensitivity (AS/AI), and linguistic ordering rules. When used in a SELECT, ORDER BY, or WHERE clause, COLLATE applies only to that expression. When used in CREATE/ALTER TABLE it becomes the permanent collation for the column, affecting all future queries unless another COLLATE is supplied. Different databases ship with dozens of collations such as 'en_US', 'utf8_general_ci', or 'Latin1_General_CS_AS'. Mixing collations in the same expression without explicitly reconciling them raises an error. Performance is similar to default comparisons, but using binary collations can speed up large sorts at the cost of human-friendly ordering.

SQL COLLATE Syntax

-- Expression level
expression COLLATE collation_name;

-- Column definition
CREATE TABLE table_name (
  col VARCHAR(100) COLLATE collation_name
);

-- Changing a column
ALTER TABLE table_name
  ALTER COLUMN col TYPE VARCHAR(100) COLLATE collation_name;

SQL COLLATE Parameters

• expression (string) - The string literal, column, or concatenated expression to which the collation is applied.
• collation_name (string) - The target collation such as 'utf8mb4_unicode_ci' or 'French_CI_AI'.

Example Queries Using SQL COLLATE

-- Force case-insensitive comparison just for this filter
SELECT username
FROM users
WHERE username COLLATE utf8mb4_general_ci = 'galaxy';

-- Sort names using German rules (ä after a)
SELECT full_name
FROM customers
ORDER BY full_name COLLATE "de_DE";

-- Create a table storing Spanish text with accent-sensitive ordering
CREATE TABLE blog_posts (
  title TEXT COLLATE "es_ES",
  body  TEXT
);

Expected Output Using SQL COLLATE

Queries return or order rows according to the specified collation
Table or column definitions inherit the chosen collation for all subsequent operations

Use Cases with SQL COLLATE

Force case-insensitive or accent-insensitive searches without changing database defaults.
Implement per-column locale rules in multilingual applications.
Resolve "illegal mix of collations" errors when joining tables using different defaults.
Optimize performance by switching to binary collation for massive sorts.

Common Mistakes with SQL COLLATE

Assuming COLLATE changes the stored data; it only changes comparison behavior.
Mixing different collations in the same comparison without explicitly casting or re-collating.
Forgetting that ORDER BY COLLATE applies after functional expressions, potentially altering expected sort order.
Using a collation not installed in the database, which raises an error.

First Introduced In

SQL:1999 standard (vendor-specific support appeared earlier in SQL Server 7.0 and MySQL 4.1)

Frequently Asked Questions

What is a collation?

A collation is a set of rules that dictates how characters are compared and sorted, including case and accent handling.

How do I list available collations in my database?

Use a catalog query: - PostgreSQL: SELECT * FROM pg_collation;- MySQL: SHOW COLLATION;- SQL Server: SELECT name FROM sys.fn_helpcollations();

Is COLLATE case-insensitive by default?

It depends on the collation chosen. Many names ending in _ci are case-insensitive, while _cs signifies case-sensitive.

Can COLLATE be used in indexes?

Yes. Defining a column with a specific collation means any index on that column follows the same comparison rules, enabling efficient locale-aware lookups.