Purpose
NLSSORT() returns a collation key for given characters based on the explicitly or implicitly specified collation.
The NLSSORT() function uses a specified collation to generate a collation key. The collation key is a byte string for sorting characters based on the specified collation.
The length of the collation key is specified by the MAX_STRING_SIZE parameter. If MAX_STRING_SIZE is set to EXTENDED, the maximum length of the return value is 32,767 bytes. If the collation key exceeds the maximum length allowed, execution of the function fails and the error "unable to create the collation key" is returned.
Syntax
NLSSORT(char [, 'nlsparam' ])
Parameters
| Parameter | Description |
|---|---|
| char | This parameter supports both literals and expressions (including column names). It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. If the input characters are of the CLOB data type, the characters are implicitly converted. |
| nlsparam | The collation. The format of nlsparam is fixed to NLS_SORT = collation. collation specifies the name of the language collation. OceanBase Database V4.2.0 and later support collations based on the GB18030_2022 character set for simplified Chinese. The collations available for simplified Chinese character sets include:
nlsparam is omitted, this function uses the default collation of the character set. |
Examples
obclient> CREATE TABLE tbl1(PK INT, name VARCHAR(25));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,'Fukesi'),(2,'Jingcha'),(3,'Dishi'),(4,'Linken'),(5,'Yalisangnazhou'),(6,'Huashengdun'),
(7,'Daier'),(10,'Langxun');
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+-----------------+
| PK | NAME |
+------+-----------------+
| 1 | Fukesi |
| 2 | Jingcha |
| 3 | Dishi |
| 4 | Linken |
| 5 | Yalisangnazhou |
| 6 | Huashengdun |
| 7 | Daier |
| 10 | Langxun |
+------+-----------------+
8 rows in set
obclient> SELECT * FROM tbl1 ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');
+------+-----------------+
| PK | NAME |
+------+-----------------+
| 7 | Daier |
| 3 | Dishi |
| 1 | Fukesi |
| 6 | Huashengdun |
| 2 | Jingcha |
| 10 | Langxun |
| 4 | Linken |
| 5 | Yalisangnazhou |
+------+-----------------+
8 rows in set