Table of Contents

About

Different languages have different sort orders. In addition, different cultures or countries that use the same alphabets may sort words differently.

For example, in Danish, Æ is after Z, while Y and Ü are considered to be variants of the same letter.

Sort order can:

  • be case-sensitive or case-insensitive.
  • ignore or consider diacritics.
  • be phonetic
  • be based on the appearance of the character. For example, sort order can be based on the number of strokes in East Asian ideographs.

Another common sorting issue is combining letters into a single character. For example, in traditional Spanish, ch is a distinct character that comes after c, which means that the correct order is: cerveza, colorado, cheremoya. This means that the letter c cannot be sorted until Oracle has checked whether the next letter is an h.

Oracle provides the following types of sorts:

  • Binary sort
  • Monolingual linguistic sort
  • Multilingual linguistic sort

Type of Sort

Binary

One way to sort character data is based on the numeric values of the characters defined by the character encoding scheme.

This is called a binary sort. Binary sorts are the fastest type of sort. They produce reasonable results for the English alphabet because the ASCII and EBCDIC standards define the letters A to Z in ascending numeric value.

Note:

  • In the ASCII standard, all uppercase letters appear before any lowercase letters.
  • In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters.

When characters used in other languages are present, a binary sort usually does not produce reasonable results. For example, an ascending ORDER BY query returns the character strings ABC, ABZ, BCD, ÄBC, when Ä has a higher numeric value than B in the character encoding scheme. A binary sort is not usually linguistically meaningful for Asian languages that use ideographic characters.

NLS_SORT

  • Syntax NLS_SORT = { BINARY | linguistic_definition }
  • Default value: Derived from NLS_LANGUAGE

Documentation / Reference