NLSSORT

Returns the sort key value for the given string.

SQL syntax

NLSSORT (String [,'NLS_SORT = SortName'])

Parameters

NLSSORT has the following parameters:

Parameter Description

String Supported data types for String are CHAR, VARCHAR2, NCHAR and NVARCHAR2. Given the String, NLSSORT returns the sort key value used to sort the String.

['NLS_SORT = SortName'] SortName is either the linguistic sort sequence or BINARY. If you omit this parameter, then the default sort sequence for your session is used. Append to the SortName the suffix -ai for accent-insensitive sorting or -ci for case-insensitive sorting. For more information on acceptable linguistic SortName values, see "Supported linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide.

Parameter	Description
`String`	Supported data types for `String` are CHAR, VARCHAR2, NCHAR and NVARCHAR2. Given the String, NLSSORT returns the sort key value used to sort the `String`.
['NLS_SORT = `SortName`']	`SortName` is either the linguistic sort sequence or BINARY. If you omit this parameter, then the default sort sequence for your session is used. Append to the `SortName` the suffix `-ai` for accent-insensitive sorting or `-ci` for case-insensitive sorting. For more information on acceptable linguistic `SortName` values, see "Supported linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide.

Description

The returned sort key value is of type VARBINARY.
You can create a linguistic index for linguistic comparisons.

Examples

The following example illustrates sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of the string. In addition, the example shows the same results can be obtained by using the ALTER SESSION... SET NLS_SORT statement.

Command> CREATE TABLE nsortdemo (name VARCHAR2 (15));
Command> INSERT INTO nsortdemo VALUES ('Gaardiner');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaberd');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaasten');
1 row inserted.
Command> # Perform Sort
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaardiner >
< Gaasten >
< Gaberd >
3 rows found.
Command> #Use function to perform sort
Command> SELECT * FROM nsortdemo ORDER BY NLSSORT (name, 'NLS_SORT = XDanish');
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command># comparison operation
Command> SELECT * FROM nsortdemo where Nnme > 'Gaberd';
0 rows found.
Command> #Use function in comparison operation
Command> SELECT * FROM nsortdemo WHERE NLSSORT (name, 'NLS_SORT = XDanish') >
> NLSSORT ('Gaberd', 'NLS_SORT = XDanish');
< Gaardiner >
< Gaasten >
2 rows found.
Command> #Use ALTER SESSION to obtain the same results
Command> ALTER SESSION SET NLS_SORT = 'XDanish';
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command> SELECT * FROM nsortdemo where name > 'Gaberd';
< Gaardiner >
< Gaasten >
2 rows found.

The following example creates a linguistic index:

Command> CREATE INDEX danishindex ON nsortdemo (NLSSORT (name, 'NLS_SORT = XDanish'));
Command> INDEXES N%;
Indexes on table USER1.NSORTDEMO:
  DANISHINDEX: non-unique T-tree index on columns:
    NLSSORT(NAME,'NLS_SORT = XDanish')
  1 index found.
1 table found.