The CHARSET_COLL collation produces a binary ordering based on the current client character set. The NOT CASESPECIFIC version is designed to produce the results if the strings were converted to uppercase and then sorted in binary order on the client.
CHARSET_COLL gives you a collation that matches the client character set. For example, if the client character set is UTF8, then the collation matches UTF8 order.
Comparison Rules
Strings are compared character-by-character.
The comparison rules for CHARSET_COLL are:
- The shorter string, if any, is padded with the pad character for the character set.
- If the comparison is not case specific, lowercase characters are mapped to their uppercase counterparts.
- If the strings are now identical, the equality relation holds. Otherwise, the first pair of characters that are not equal determine the collating sequence.
- If both characters are in the repertoire of the current client character set, then the binary ordering of the two characters in the client form-of-use becomes the ordering of the two strings.
- If one of the characters is not within the repertoire of the current client character set, then the error character is used as the collation point for that character.
- If both characters being compared are outside the repertoire of the current client character set, then the binary ordering of the characters (case blind or case specific, as appropriate) in the Unicode form-of-use becomes the ordering of the two strings.
Making CHARSET_COLL the Default Collation Sequence
You can specify CHARSET_COLL as the default user collation with the CREATE USER or MODIFY USER statements.
You can also use the SQL SET SESSION COLLATION CHARSET_COLL statement to override any user defaults.