Unicode Fields and Collation

Unicode fields are using collation for sorting and comparison instead of ASCII character codes. This causes some behavioral differences between raw fields and Unicode fields in APPX that you should be aware of.

Sorting a Unicode field can have a different result from sorting a Raw field. Since APPX uses collation for sorting Unicode fields, depending on the collation used, you may see different sorting results. Here is an example that shows the differences in result of sorting a Unicode field and a raw field with similar values:

RAW -------- Unicode

In this case APPX uses the default system collation on a Windows 10 machine which is "en_US".

Queries also use collation to sort Unicode fields. So, sorting a Unicode field and a raw field with similar data may result in different sort order. You can use "APPX_QSORT_COL_NAME" and "APPX_QSORT_COL_STRENGTH" environment variables to change the collation used in query processes to sort Unicode fields. If these environment variables are not specified APPX uses the system's collation which also can be overridden (see below).

Comparing Unicode Alpha fields may surprise you. If you are using Raw alpha field and run a statement like:

The result will be TRUE since ASCII code for character 'a' is larger than 'A'. However, if you run the same statement on Unicode fields (based on the collation used) the result may be FALSE. In case of "en_US" collation, the result will be FALSE but if you use "en_US_POSIX" collation, the result will be TRUE.

You can override the collation APPX uses by setting “APPX_UCODE_COL_NAME” and “APPX_UCODE_COL_STRENGTH” environment variables. However, there are some things you need to consider before doing that:

• Since APPX creates the key indexes for files by relying on the collation (if the key field is Unicode), changing the collation means that you must rebuild the key files for files which have Unicode fields in their keys. Be certain to do this otherwise you may experience data corruption.
• If different sessions using different collation, modifying records with Unicode key fields can result in data corruption. So, make sure no one is logged into the system when you are setting a new collation and rebuilding key files.
• If you use invalid collation, Appx uses the best alternative collation for you.
• "APPX_QSORT_COL_NAME", "APPX_QSORT_COL_STRENGTH" environment variables override Appx collation for query processes. Note: setting these environment variables for queries is harmless and doesn’t need key file rebuild. So, it is a good idea to test different collations and strengths with Queries using these environment variables before changing the collation in APPX.

You can set the COL_NAME to a locale name. Best collation then is chosen for the selected locale. For example, if you specify "en_BB" (English - Barbados), Appx looks for a collation with that name and If it couldn’t find one then the best alternative collation will be chosen, which in this case is "en". You can refer to https://www.localeplanet.com/icu/ for a list of locales.

Valid Strengths for collations are:

• -1: DEFAULT
• 0: PRIMARY
• 1: SECONDARY
• 2: TERTIARY
• 3: QUATERNARY
• 15: IDENTICAL

You can find more information about each of them at https://unicode-org.github.io/icu/userguide/collation/concepts.html#comparison-levels