Consider two vertical bars with a horizontal bar between them: H. This H is a glyph. To us, this glyph is a symbol — it's the letter "Aitch" and it appears in English words. Thus, a "letter" is a combination of a glyph and a meaning. (The importance of this will become apparent as you go through this chapter.) We know that H is the eighth letter of an alphabet. Or — continue
Page 96
in SQL terms — H is the eighth symbol in a character repertoire. While an alphabet is a familiar example of a character repertoire, any computer will also support a repertoire containing digits,
punctuators, and special characters as well as glyphless (non-printing) characters such as the \0 which terminates strings in C.
However, no computer can handle the letter H by itself. It can only handle numbers, so we have a convention that the letter H will be represented by some number, such as 72. If we all agree that
"72 is the code for H, 74 is the code for J . . . " and so on for every character in the repertoire, we can make a computer character set. Every character set has two parts: a character repertoire and an agreement on how the repertoire's characters will be encoded as numbers. (The SQL Standard calls the encoding scheme itself a Form-of-use.)
Internationally, there are many character sets and many names for them. When using SQL, you'll be concerned mainly with the ones that the International Organization for Standardization (ISO) has published standards for.
• ISO 646 (1965) also known as US-ASCII, DIN 66003, etc.
• ISO 8859-1 (1985) also known as LATIN 1 and Windows Code Page 1252.
• ISO 8859-2 (1987) also known as LATIN 2 and Windows Code Page 1250.
• . . . and several others which we'll describe as we go along.
The numeric codes between 0 and 127 have the same meaning in every ISO standard; differences exist only for codes greater than 127. In each standard, the codes between 0 and 127 include punctuation, digits, special characters, and (what's most important for this discussion) — the uppercase letters A to Z (for example, the code for H is indeed 72), and the lowercase letters a to z. Thus, all character sets start with Latin.
Latin
Cicero never saw a W or a lowercase j, but medieval and modern Latin have 26 letters, each of which has two forms: uppercase and lowercase. These are the simple Latin letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ -- simple uppercase Latin letters abcdefghijklmnopqrstuvwxyz -- simple lowercase Latin letters
These letters appear in all standard computer character sets including 7-bit ASCII and EBCDIC.
They even appear in character sets for languages which don't use Latin. For instance, character sets used in Russia (such as KOI8R or ISO 8859-5) contain all the Latin letters with the same codes as 7-bit ASCII; the Russian Cyrillic letters follow the Latin letters.
Because they are universal, the Latin letters are vital for computer languages. In SQL, all
<keyword>s and Standard-generated <regular identifier>s (or names) are made with Latin letters (though they may also contain digits and the underscore character).
The letters of the Latin alphabet are in alphabetic order. That might sound . . . well,
"Duh" . . . but it helps to point out the most fundamental point about a collation or collating
sequence — namely, that a collation represents a fixed order and that order is known to most members of the general public. They expect to see words and names sorted that way (though they don't expect A to sort before a — that's a computer thing).break
Page 97
English
English uses the Latin alphabet but adds rules when sorting. To set the scene for the complexities that follow, here are the rules for English sorting. (We'll naìvely pretend that when we write à l'anglaise, we don't need even a soupç on of those pesky "accented letters" for names like El Niño — we'll look at English only. All examples come from The Concise Oxford Dictionary Of Current English.)
Preprocess (Map to Simple Latin Uppercase Letters)
Preprocessing involves (a) stripping accents off all accented characters, e.g., Ò becomes O, (b) eliminating all special characters such as hyphen or space or apostrophe or dot, and (c) changing ligatures such as Æ to double-characters such as AE.
Sort
Use the alphabetical order: ABCDEFGHIJKLMNOPQRSTUVWXYZ Tiebreakers
• Tiebreakers apply only if two words are otherwise equal.
• If a special character (such as a hyphen or apostrophe) was stripped in the preprocess stage, the word containing it follows. For example: "cant" before "can't" before "Cant." and "recollect"
before ''re-collect" and "nones" before "non est" and "francophone" before "franc tireur".
• If a word contained an accented character, it follows. For example: "lame" before "lamé."
• If a word contains a capital letter, it doesn't matter. For example: "Negus" before "negus," "AR"
before "Ar" — but, "dan" before "Dan," "ma" before "MA." (We acknowledge that some people believe it does matter; we report only what actually happens.)
About 50% of English-world phone books have additional rules:
• Mc and Mac appear before M (sometimes M' does too, but only in Ireland). For example:
"McDonald" before "Macdonald" before "Maastricht."
• Ste and St appear as if they were spelled "Saint." For example: "St John" before "Saan Stores Limited."
• In these cases, we effectively combine a group of letters into one and place the result in the hidden interstices between O and M, or between R and S. It looks odd, but many languages have two-letter combinations (called digraphs) which fit between primary letters of the alphabet.
From all this, we see that English sorting involves, in addition to the basic A-to-Z stuff, several special collation rules — including mapping, ligatures ("one character maps to two"),
tiebreaking, and digraphs ("two characters map to one"). Nobody makes a fuss about English because the special cases are rare. But special cases in non-English languages are not rare at all — they are the norm, so it's the non-English collation rules that get the attention.break
Page 98
French
French words have:
a or e with Grave accent ès à
a or e with Acute accent Chrétien résumé a or i or o with Circumflex hôte côte
c with Cedilla soupç on ç a
i or e or o with Diaeresis naïf coö pë rant
In simple cases, the French sorting rules are like the English rules.
• Accented characters map to their Latin uppercase equivalents ("résumé" comes after "rester"
but before "retard'').
• Words with accented characters come after words with none ("à" comes after "a" — but that's only a tiebreaker; "à" comes before "abbé."
Then things get complicated.
• For words with multiple accented characters, the word with the last accented character follows.
For example: "pêche" before "péché."
• France and French Canada don't capitalize accented letters the same way. In France, the
uppercase of the word "résumé" is "RESUME" — there are no capital letters with accents, which is why the old PC-DOS Character set (IBM extended ASCII) could dispense with the letters É, Ó , etc. But French Canadians capitalize the word "résumé" and keep the accents: "RÉSUMÉ."
This shows us that we have more characters, another collation rule, and conflicting ideas for changing lowercase to uppercase.
German
German words have:
a or o or u with umlaut Mädchen ö stlich Führer
sharp S or Eszet bißchen
Sharp S's similarity to a Greek Beta is just coincidence; it is really a ligature which maps to "ss."
This is the only character in any Latin alphabet which is always lowercase.
There are three ways to sort characters with umlauts, depending on the German-speaking country and the application.
1. In the German DIN-1 official standard, Ä = A and Ö = O and Ü = U. That is, accented characters map to unaccented equivalents. This is the standard used for dictionaries, book indexes, or any lists of words. Thus, Führer = Fuhrer.
2. In the German DIN-2 official standard, Ä = AE and Ö = OE and Ü = UE. That is, accented characters map to unaccented character plus E. This is the standard used for phone books, voter lists, or any lists of names in Germany. Thus, Gö bbels = Goebbels and Düsseldorf =
Duesseldorf.break
Page 99
3. In the Austrian unofficial standard, Ä > AZ and Ö > OZ and Ü > UZ. That is, each accented character is treated as a separate letter between A and B or O and P or U and V. This is the standard used for lists of names in Austria. Thus, Müller > Mzilikaze.
The Swiss use DIN-1 for all sorting.
The German tiebreaker rules are: unaccented before accented (this rule is universal), Eszet before umlaut, and umlaut before any other accent. The following table shows three lists sorted
according to DIN-1 (Zürich phone book), DIN-2 (Berlin phone book), and Austrian collation (Vienna phone book).
DIN-1 DIN-2 Austrian
Moeller Moeller Moeller
Moffat Mö ller Moffat
Mö ller Moffat Morse
Morse Morse Mö ller
Morse Morse Mö ller
Mö se Mö se Mö se
Motler Motler Motler
At this point, you may be wondering what happens if you click "use German collating sequence"
on some Windows dialog box. Well, Microsoft seems to prefer DIN-1, but the point is: if you just click, you'll get a Microsoft choice which is wrong at least half the time. As we said at the beginning of this chapter; you have to know what the problem is before you know what the solutions are worth.
Spanish
Spanish words have:
tilde over n cañon
acute accent over a u o i corazó n
Some circles (such as the Spanish-speaking community in the United States) use the English style rules to sort Spanish words; all accented characters map to their simple Latin equivalents, period. However, the following list and discussion on Spanish collation refers to the traditional rules which apply in most situations.
• CH comes between C and D
• LL comes between L and M
• N comes before Ñ break
NOTE: From now on, we'll use the formula "<x> follows <y>" to mean "<x> is treated as a separate letter which follows <y> in the alphabet," as in: CH follows C.
Page 100
The Spanish CH and LL digraphs are truly separate letters. They get separate headings in the dictionaries and the Spanish alphabet really has 28 letters:
a b c ch d e f g h i j k l ll m n ñ o p q r s t u v x y z Here is an example of a sorted Spanish list:
calza calle cantor cañon culto che