Character sets and collations for character expressions

Every character expression has a character set and a collation.

Specify the character set and collation for a character expression

The default character set and collation of a character expression, such as the simple statement SELECT 'string', are defined by the character_set_connection and collation_connection variables.

You can use the following syntax of the COLLATE clause to specify the character set and collation for a character expression:

[_charset_name]'string' [COLLATE collation_name]

Here is an example:

obclient> SELECT _utf8mb4'abc' COLLATE utf8mb4_unicode_ci;
+------------------------------------------+
| _utf8mb4'abc' COLLATE utf8mb4_unicode_ci |
+------------------------------------------+
| abc                                      |
+------------------------------------------+
1 row in set

When you specify the character set of a constant to gb18030_2022 in an SQL statement, if the constant after _gb18030_2022 is a hexadecimal number, the number is converted into the corresponding character in the gb18030_2022 character set. If the constant after _gb18030_2022 is a string, its character set is set to gb18030_2022.

obclient> SELECT _gb18030_2022 0xCDE5 AS c FROM DUAL;
+-----+
| c   |
+-----+
| B   |
+-----+
1 row in set


obclient> SELECT _gb18030_2022 'B' AS c FROM DUAL;
+-----+
| c   |
+-----+
| B   |
+-----+
1 row in set

Select the character set and collation for a character expression

You can select the character set and collation for a character expression by using the following methods in OceanBase Database:

• If you specify both the _charset_name and COLLATE collation_name parameters, the charset_name character set and collation_name collation are used.

• If you specify only the _charset_name parameter, the specified charset_name character set and the associated default collation are used. To view the default collation for each character set, execute the SHOW CHARACTER SET statement.

• If you specify only the COLLATE collation_name parameter, the character set specified for character_set_connection and the default collation specified for collation collation_name are used. collation_name must be a collation supported by the default character set.

• If neither CHARACTER SET nor COLLATE is specified, the character set specified for character_set_connection and the default collation specified for collation collation_name are used.