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 a specified collation.
The length of the sort key is specified by the MAX_STRING_SIZE parameter. If MAX_STRING_SIZE=EXTENDED, the maximum length of the return value is 32,767 bytes. If the sort 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 | Specifies the sorting rule. The fixed format of nlsparam is NLS_SORT = collation. collation refers to the language sorting rule name. Starting from OceanBase Database V4.2, a new sorting method based on the GB18030_2022 character set is now supported for simplified Chinese sorting. The collations available for simplified Chinese character sets include:
nlsparam is omitted, this function uses the default sorting rule 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