HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

What does "collation" mean?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dbaterminologystackoverflow
doesmeanwhatcollation

Problem

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?

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#4270, answer score: 16

Revisions (0)

No revisions yet.