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. A sort key is a byte string that is used to sort characters based on 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 an error message "unable to create the collation key" will be returned.
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 name of the collation. Starting from OceanBase Database V4.2, the database supports the GB18030_2022 character set for Chinese sorting. 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