What does collation mean in database parlance?

Question

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 ?

Answer 1

Basically, how to sort and compare strings.

Examples:

Comparing:

• Accent: à vs ä vs a
• Case: A vs a

Sorting:

• Swedish: z -> å -> ä -> ö
• Danish: z -> æ -> ø -> å

Special character equivalence

• German ß = ss

Wikipedia

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