Oracle PL/SQL Programming, 5th Edition

String Sort Order

Oracle provides advanced linguistic sort capabilities that extend far beyond the basic A-Z sorting you get with an ORDER BY clause. The complexities found in international character sets do not lend themselves to simple alphabetic sort, or collation, rules. Chinese, for example, includes approximately 70,000 characters (although many are not used regularly). Not exactly something you can easily put into song like the ABCs! Also, not something that can be defined by simple sort rules.

String sort order is an obvious programming problem that is often overlooked in globalization until a product makes its way to the test team. Ordering the names of employees, cities of operation, or customers is much more complicated than “A comes before B.” Consider the following factors:

• Some European characters include accents that change the meaning of the base letter. The letter “a” is different from “ä.” Which letter should come first in an ORDER BY?

• Each locale may have its own sort rules, so a multilingual application must be able to support different sort rules based on the text. Even regions that use the same alphabet may still have different sort rules.

Oracle provides three types of sorts: binary, monolingual, and multilingual.

The Unicode Consortium makes its collation algorithm public, so we can compare the output from our queries for these three types of sorts with the expected results shown at http://www.unicode.org/charts/collation/.

Binary Sort

The binary sort is based on ...