1

I can't get my head around the collation as compared to the character set. What is collation actually used for ?

1 Answers1

1

I'm not sure if this question is too broad. But let me take a stab at it.

A character set is something that we see on the screen. So, you probably have a pretty good idea of what the letter "a" is. And, you can accept that "A" is also a letter.

You can modify letters as they appear with fonts. So, a and a are still the same letter, but they look different. This is not related to the collation, but intended to get you to think about the subject.

Collations tell use whether two letters are the same. So, you can have a case-insensitive collation that says that "A" and "a" are the same. Or, it can be case-sensitive, saying that they are different.

This is a very basic example. As you extend characters into other languages, the problems multiply. Are accented characters the same as unaccented characters? Are enyas in Spanish the same as an n? And so on.

These are the questions that collations answer. A character set translates sequences of bits into characters that can be displayed. A font tells you what the character looks like. A collation allows us to compare two characters to know if they are the same, and to know their ordering in a dictionary.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Can the collation be then used for case insensitive search on a particular field ? –  Jul 27 '15 at 03:08
  • 1
    @RalphBrown . . . Yes, in a sense. The collation is a property of a string value. It can be defined for a column, database, or server (so it inherits the value if not explicitly given). If you use `COLLATE``, then you an specify it. However, the collations need to be compatible on for both strings involved in the comparison. (That is, it is a property of the strings, not the comparison.) – Gordon Linoff Jul 27 '15 at 03:14