SQL COLLATION

What is SQL COLLATION?

COLLATION defines or overrides the set of rules used to compare and sort character data.

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 COLLATION: SQL Server, PostgreSQL, MySQL, MariaDB, Oracle, SQLite (partial), Snowflake

SQL COLLATION Full Explanation

COLLATION (often used via the COLLATE clause) tells the database which locale-specific rules to apply when comparing, sorting, or hashing character strings. A collation bundles language, culture, and case-sensitivity rules, affecting ORDER BY results, equality checks, DISTINCT, GROUP BY, indexes, and joins. Collations can be assigned at the server, database, column, expression, or literal level, with later scopes overriding earlier ones. Choosing the correct collation prevents unexpected sort orders and comparison mismatches between character sets. Mixing collations in a single query triggers implicit or explicit collation coercion; when coercion is impossible, the engine raises an error. Not all collations are available in every database or OS, and some operations (e.g., LIKE optimizations) may use slower code paths with non-default collations.

SQL COLLATION Syntax

-- Column-level
CREATE TABLE employees (
  last_name VARCHAR(100) COLLATE "en_US" NOT NULL
);

-- Expression-level
SELECT last_name COLLATE "de_DE" AS de_sorted
FROM employees
ORDER BY de_sorted;

SQL COLLATION Parameters

collation_name (string) - Locale identifier or predefined collation to apply

Example Queries Using SQL COLLATION

-- 1. Override sort order for one query
SELECT last_name COLLATE "fr_FR" AS french_sort
FROM employees
ORDER BY french_sort;

-- 2. Create case-insensitive column in MySQL
CREATE TABLE tags (
  tag VARCHAR(50) COLLATE utf8mb4_unicode_ci PRIMARY KEY
);

-- 3. Resolve cross-database join in SQL Server
SELECT a.name, b.description
FROM db1.dbo.products a
JOIN db2.dbo.products_desc b
  ON a.name COLLATE Latin1_General_CI_AS = b.name;

Expected Output Using SQL COLLATION

Queries run using the specified collation rules
ORDER BY returns rows in locale-specific sequence, comparisons respect case or accent sensitivity as defined, and joins succeed even when source columns differ in default collation

Use Cases with SQL COLLATION

Sorting names according to a specific language alphabet
Performing case-insensitive or accent-insensitive searches
Joining tables from databases that use different default collations
Creating indexes optimized for a target locale
Ensuring deterministic string comparisons in multi-tenant applications

Common Mistakes with SQL COLLATION

Assuming default database collation applies everywhere
Mixing columns with incompatible collations without explicit CAST or COLLATE
Forgetting that indexes may become non-seekable with certain collations
Believing COLLATION affects binary or numeric data types
Using unavailable or misspelled collation names

First Introduced In

SQL-92 (standardized), extended in SQL:1999; vendor support predates the standard

Frequently Asked Questions

What is the difference between character set and collation?

A character set defines the valid symbols and their binary encoding, while a collation defines how those symbols are compared and ordered. Many collations can exist for one character set.

How can I check the current collation of a column?

In SQL Server, query sys.columns. In MySQL, use SHOW FULL COLUMNS or INFORMATION_SCHEMA.COLUMNS. PostgreSQL exposes it via the collcollation column in pg_attribute.

Will changing a database’s default collation rewrite data?

Altering the default collation updates metadata only; stored data remains unchanged. However, indexes may need to rebuild and queries without explicit COLLATE will now use the new rules.

Are collations portable across databases?

No. Collation names and behaviors are vendor-specific. Always check the target DBMS and specify portable Unicode collations where possible.