Purpose
This function returns the sort key (Sortkey) of a character string based on the specified collation, which can be specified explicitly or implicitly.
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 collation key is affected by the MAX_STRING_SIZE parameter. If MAX_STRING_SIZE=EXTENDED, the maximum length of the returned value is 32767 bytes. If the collation 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 specifies the language collation name. OceanBase Database V4.2 and later versions support new Chinese collations based on the GB18030_2022 character set. For the Chinese character set, the following three Chinese collations are supported:
nlsparam is omitted, the 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,'Foxy'),(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 | Foxy |
| 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_PINYIN_M');
+------+-----------------+
| PK | NAME |
+------+-----------------+
| 7 | Dell |
| 3 | Taxi |
| 1 | Foxy |
| 6 | Washington |
| 2 | Police |
| 10 | Lucent |
| 4 | Lincoln |
| 5 | Arizona |
+------+-----------------+
8 rows in set