EMZETT.
Login

Emzett Wiki

Sorting and collation: how texts are compared

Properties1
tagsvertiefung, datenbanken, text

1 min read

Sorting and collation: how texts are compared

In short: The collation defines how strings are compared and sorted: upper/lower case, accents, language rules. It decides whether “ä” comes before or after “z”.

In more detail: Computers do not compare text by the alphabet but by rules. Simple rules (by code point) are fast, language-correct rules (Unicode collation) are more elaborate but make more sense to people.

In Depth

  • Binary: Comparison by byte or code point value. “Z” comes before “a”, “ä” at the very end. Fast but unfamiliar to users.
  • Language-aware: German sorts “ä” with “a”, Swedish at the end of the alphabet. In databases as de_DE, utf8mb4_unicode_ci, und-x-icu etc.
  • Levels: With or without case sensitivity (ci/cs), with or without accents (ai/as).
  • Consequences: Uniqueness (UNIQUE) depends on the collation: “Anna” and “anna” count as equal under ci. Indexes are collation-dependent.
  • Upgrading: If the operating system or library version changes the sort rules, indexes can become invalid (PostgreSQL warns about this).
  • Programming: The default string comparison in programming languages is usually binary. For user lists use Intl.Collator, locale.strxfrm or similar.

See also: Unicode, UTF-8