Alex Rivera | Logout

What does collation mean?

Asked 2010-12-27T12:47:10.847
168

What does collation mean in SQL, and what does it do?

Edit
Report

2 Answers

51

Besides the "accented letters are sorted differently than unaccented ones" in some Western European languages, you must take into account the groups of letters, which sometimes are sorted differently, also.

Traditionally, in Spanish, "ch" was considered a letter in its own right, same with "ll" (both of which represent a single phoneme), so a list would get sorted like this:

  • caballo
  • cinco
  • coche
  • charco
  • chocolate
  • chueco
  • dado
  • (...)
  • lámpara
  • luego
  • llanta
  • lluvia
  • madera

Notice all the words starting with single c go together, except words starting with ch which go after them, same with ll-starting words which go after all the words starting with a single l. This is the ordering you'll see in old dictionaries and encyclopedias, sometimes even today by very conservative organizations.

The Royal Academy of the Language changed this to make it easier for Spanish to be accomodated in the computing world. Nevertheless, ñ is still considered a different letter than n and goes after it, and before o. So this is a correctly ordered list:

  • Namibia
  • número
  • ñandú
  • ñú
  • obra
  • ojo

By selecting the correct collation, you get all this done for you, automatically :-)

answered 2010-12-27T16:01:57.503
13

Collation defines how you sort and compare string values

For example, it defines how to deal with

  • accents (äàa etc)
  • case (Aa)
  • the language context:
    • In a French collation, cote < côte < coté < côté.
    • In the SQL Server Latin1 default , cote < coté < côte < côté
  • ASCII sorts (a binary collation)
answered 2010-12-27T13:05:58.717

Your Answer