Purpose
This function returns the sort key (Sortkey) of a character string based on the specified collation, which can be either explicitly or implicitly provided.
The NLSSORT() function generates a sort key based on the specified collation. The sort key is a byte string used to sort characters according to the specified collation.
The length of the sort key is affected by the MAX_STRING_SIZE parameter. If MAX_STRING_SIZE=EXTENDED, the maximum length of the return value is 32767 bytes. If the sort key exceeds this limit, the function will fail and return an error message "unable to create the collation key".
Syntax
NLSSORT(char [, 'nlsparam' ])
Parameters
| Parameter | Description |
|---|---|
| char | This parameter supports literals and expressions (including column names). The data type can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If the input data type is CLOB, it will be implicitly converted. |
| nlsparam | Specifies the collation. The fixed format of nlsparam is NLS_SORT = collation. collation is the name of the language collation. Starting from OceanBase Database V4.2, the database supports sorting based on the GB18030_2022 character set for Simplified Chinese. For the Simplified Chinese character set, the following three sorting methods are supported:
nlsparam is omitted, the function uses the default collation for the character set. |
Examples
obclient> CREATE TABLE tbl1(PK INT, name VARCHAR(25));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,'Fox'),(2,'Police'),(3,'Taxi'),(4,'Lincoln'),(5,'Arizona'),(6,'Washington'),
(7,'Dell'),(10,'Lucent');
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+-----------------+
| PK | NAME |
+------+-----------------+
| 1 | Fox |
| 2 | Police |
| 3 | Taxi |
| 4 | Lincoln |
| 5 | Arizona |
| 6 | Washington |
| 7 | Dell |
| 10 | Lucent |
+------+-----------------+
8 rows in set
obclient> SELECT * FROM tbl1 ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN2_M');
+------+-----------------+
| PK | NAME |
+------+-----------------+
| 7 | Dell |
| 3 | Taxi |
| 1 | Fox |
| 6 | Washington |
| 2 | Police |
| 10 | Lucent |
| 4 | Lincoln |
| 5 | Arizona |
+------+-----------------+
8 rows in set