Localized order

Localized order is an order of the characters that relates to a natural language. The locale defines the order of the characters in the localized order.

For example, even though the character À might have a code point of 133, the localized order can list this character after A and before B (A=65, À=133, B=66). In this case, the string B sorts after AC but before BD.

The database server uses localized order to sort columns of these data types:
  • NCHAR
  • NVARCHAR
The localized order can include equivalent characters, those characters that the database server is to consider as equivalent when it collates them. For example, if the locale defines uppercase and lowercase versions of a character as equivalent in the localized order, then the strings Arizona, ARIZONA, and arizona are collated together, as if all three strings were the same string.
Tip: The COLLATION category of the locale file specifies the localized order, if one exists. For more information, see The COLLATION category.
A localized order can also specify a collating sequence that does not match the order of code points in the character set of the locale. For example, a telephone book might require the following sort order: 
Mabin
McDonald
MacDonald
Madden
A dictionary, however, might use this collating order for the same names: 
Mabin
Madden
MacDonald
McDonald

If the GLS locale defines a localized order, the database server sorts data from NCHAR and NVARCHAR columns in this localized order. For an example of data sorted in a localized order, see Data set for localized order of the abonnés table.

HCL OneDB™ supports the SET COLLATION statement, which can specify a localized collation different from the DB_LOCALE setting. The scope of the non-default collating order is the current session, but database objects that perform collation, such as indexes or triggers, use the collating order from the time of their creation when they sort NCHAR or NVARCHAR values.

After the SET COLLATION statement has specified a localized collation order, and you have completed all of the sorting tasks that require that localized order, you can restore the collation that the DB_LOCALE setting implies by issuing the SET NO COLLATION statement of SQL.

The SET COLLATION statement only affects localized collation operations that the database server performs. Sorting of NCHAR or NVARCHAR data values by the client always follows the collation order of the CLIENT_LOCALE setting, and ignores any SET COLLATION specifications. For more information about the environment variables that can define the client locale or the server locale, see Locales in the client/server environment.