O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required