The COLLATE operator specifies a collation for a character type. You can use the COLLATE operator to specify a collation for a character type or for an expression.
The COLLATE operator is a unary suffix operator with the same precedence as other unary operators. 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 spaces, 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
You can execute the SHOW COLLATION; statement to view the collations supported by 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