Collation tailoring options

If you choose the UCA collation when you create a database, you can optionally specify collation tailoring options.

If you do not choose UCA as the collation, you can still use tailoring syntax to control case sensitivity. You can also specify tailoring options when comparing or sorting data using the COMPARE and SORTKEY functions.

Swedish collation tailoring

To tailor a UCA collation to conform to the Swedish Academy's 2005 standards in which V and W are considered to be different characters at the primary level, specify UCA (locale=swe;sorttype=phonebook). Without sorttype=phonebook, V and W are considered to be the same character in the Swedish locale.

Japanese collation tailoring

UCA distinguishes some Hiragana and Katakana letters only at the tertiary level and those differences are lost in case insensitive collations. To tailor a UCA collation to define primary level differences between all Hiragana letters, as well as primary-level differences between all Katakana letters, specify UCA (locale=ja;sorttype=direct;...). Although these tailoring options do not provide absolute correct sorting semantics, they do provide correct equality semantics.

Summary of collation tailoring options

Collation tailoring options take the form of keyword-value pairs. Following is a table of the supported keywords, including their allowed alternate forms, and their allowed values.

Note Databases created with collation tailoring options cannot be started using a version 10.0.0 or earlier database server.

Keyword	Collation	Alternate forms	Allowed values
Locale	UCA	(none)	Any valid locale code. For example, en.
CaseSensitivity	All supported collations	CaseSensitive, Case	respect Respect case differences between letters. For the UCA collation, this is equivalent to UpperFirst. For other collations, it depends on the collation itself. ignore Ignore case differences between letters. UpperFirst Always sort uppercase first (Aa). LowerFirst Always sort lowercase first (aA).
AccentSensitivity	UCA	AccentSensitive, Accent	respect Respect accent differences between letters. ignore Ignore accent differences between letters. French Respect accent sensitivity with French rules.
PunctuationSensitivity	UCA	PunctuationSensitive, Punct	ignore Ignore differences in punctuation. primary Use first level sorting (consider letter, only). For example, a > b. quaternary Use fourth level sorting: consider letter first, then case, then accent, and then punctuation. For example, multiByte, multibyte, multi-byte, and multi-Byte, are sorted as: multiByte multibyte multi-Byte multi-byte You cannot specify quaternary with a case- or accent-insensitive database.
SortType	UCA	(none)	The type of sort to use. Possible values include: phonebook traditional standard pinyin stroke direct posix big5han gb2312han For more information about these sort types, see Unicode Technical Standard #35.