The COLLATE operator specifies how characters are ordered, and allows you to reset the collation of a character data type or specify the collation for an expression.
COLLATE is a postfix unary operator, and has the same precedence as other unary operators, but it is executed after all prefix unary operators. The COLLATE operator can be applied to expressions of the VARCHAR2, CHAR, NVARCHAR, or NCHAR data type.
The COLLATE operator is followed by a parameter collation_name, which specifies the collation to be set or used. If the collation name contains a space, it must be enclosed in double quotation marks (").
Here is an example:
obclient> CREATE TABLE t1(c1 VARCHAR(20));
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES('a'),('b'),(1);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t1 ORDER BY c1 COLLATE utf8mb4_general_ci;
+------+
| C1 |
+------+
| 1 |
| a |
| b |
+------+
3 rows in set
The following example uses the SHOW COLLATION command to view the collations supported by the current version of OceanBase Database.
obclient> SHOW COLLATION;
+--------------------+---------+-----+---------+----------+---------+
| COLLATION | CHARSET | ID | DEFAULT | COMPILED | SORTLEN |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | NULL | Yes | 1 |
| binary | binary | 63 | Yes | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | NULL | Yes | 1 |
| utf16_general_ci | utf16 | 54 | Yes | Yes | 1 |
| utf16_bin | utf16 | 55 | NULL | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | NULL | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | NULL | Yes | 1 |
| gb18030_chinese_ci | gb18030 | 248 | Yes | Yes | 1 |
| gb18030_bin | gb18030 | 249 | NULL | Yes | 1 |
+--------------------+---------+-----+---------+----------+---------+
11 rows in set