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 | The collation. The format of nlsparam is fixed to NLS_SORT = collation. collation specifies the name of the language collation. 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,'Fox'),(2,'Police'),(3,'Taxi'),(4,'Lincoln'),(5,'Arizona'),(6,'Washington'),
(7,'Dell'),(10,'Lumens');
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 | Lumens |
+------+------------+
8 rows in set