Oracle Database - NLS_SORT (Sorting Character parameter)

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
Oracle Database - Globalization (Localization, NLS parameter)

Globalization support enables the storing, processing and retrieval of data in native languages. The languages that can be stored in an Oracle database are encoded by Oracle Database-supported character...
Card Puncher Data Processing
Oracle Database - NLS_LANGUAGE parameter

language specifies the language used for displaying Oracle messages, sorting, day names, and month names. NLS_LANGUAGE is a Nls Parameter that define the language and implicitly the parameters:...



Share this page:
Follow us:
Task Runner