I am learning sqlite from a book which has mentioned collation and collating sequence multiple times. What does it mean exactly in the database world?
"Collation" is probably best defined on etymology.com,
late 14c., "act of bringing together and comparing,"
It hasn't changed at all in the past 600 years. "collate" means "to bring together" and whether it's the ordering of a book, chronologically or otherwise in a database, it's all the same.
As it applies to Computer Science, what we're doing is separating the storage mechanism from the ordering. You may have heard of ASCII-betical. That refers to a comparison based on the binary encoding of ASCII characters. In that system, storage and collation are two in the same. If the ASCII-standardized "encoding" ever changed the "collation" (order) would change too.
POSIX started to break that with
LC_COLLATE. But as we move into Unicode a consortium emerged to standardize collations as well: ICU.
In the SQL spec there are two extensions to provide collations,
F690: “Collation support:
F692: Extended collation support,
Includes attaching a different default collation to a column in the schema.
Essentially these provide the ability to
DROP collations, to specify them for operators and sorts, and to define a default for columns.
For more information on what SQL has to offer check out,
- PostgreSQL documentation on collations
- Oracle Linguistic Sorting and Matching
- MS SQL Server, Collation and Unicode Support, and Collations
- Character Sets and Collations in MySQL
Rather than pasting a limited example, here is the PostgreSQL test suite it's pretty extensive. Check out at least the first link and look for
'Türkiye' COLLATE "tr-x-icu" ILIKE '%KI%' AS "false"
collate.outProbably not worth checking out, but I placed it here for completeness, boring POSIX checks.
Basically, how to sort and compare strings.
- Accent: à vs ä vs a
- Case: A vs a
- Swedish: z -> å -> ä -> ö
- Danish: z -> æ -> ø -> å
Special character equivalence
- German ß = ss
Some SQL (SQL Server 2008+)
DECLARE @foo TABLE (bar varchar(2)) INSERT @foo VALUES ('z'), ('æ'), ('ø'), ('å'), ('ss'), ('ß'), ('a'), ('ä') SELECT * FROM @foo ORDER BY bar COLLATE Finnish_Swedish_100_CI_AS SELECT * FROM @foo ORDER BY bar COLLATE Norwegian_100_CI_AS SELECT * FROM @foo ORDER BY bar COLLATE Latin1_General_100_CS_AS --german (western europe) and default SELECT * FROM @foo ORDER BY bar COLLATE German_PhoneBook_100_CS_AS
Sort order. Punched cards were collated. Flat file records are collated. This is not unique to databases.
It is self explanatory for text, but for binary data stored as characters it can be trickier.