The COLLATION category

The COLLATION category can define a localized order.

When the HCL OneDB™ product needs to compare two strings, it first breaks up the strings into a series of collation elements. The database server compares each pair of collation elements according to the collation weights of each element. The COLLATION category supports the following capabilities:
  • Multicharacter collation elements define sets of characters that the database server should collate as a single unit. For example, the localized collating order might treat the Spanish double-L (ll) as a single collation element instead of as a pair of l's.
  • Equivalence classes assign the same collation weight to different elements. For example, the localized order might specify that a and A are an equivalence class (a and A are equivalent characters).

The difference in collation order is the only distinction between the CHAR and NCHAR data types and the VARCHAR and NVARCHAR data types. For more information, see Character data types.

If a locale does not contain a COLLATION category, HCL OneDB products use code-set order for collation of all character data types:
  • CHAR
  • LVARCHAR
  • NCHAR
  • NVARCHAR
  • TEXT
  • VARCHAR

The SET COLLATION statement can specify a localized collation that is different from the COLLATION setting of the locale that DB_LOCALE specifies. The scope of the collating order that SET COLLATION specifies is the current session, but database objects that can sort strings, such as constraints, indexes, UDRs, and triggers, always use the collating order from the time of their creation when they sort NCHAR or NVARCHAR values.