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.

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 pre-10.0.1 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:

  • phonebook
  • traditional
  • standard
  • pinyin
  • stroke
  • direct
  • posix
  • big5han
  • gb2312han

For more information about these sort types, see Unicode Technical Standard #35, at [external link] http://www.unicode.org/reports/tr35/.

Note

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.

See also