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	Given the String, NLSSORT returns the sort key value used to sort the String. Supported data types for String are CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB.
['NLS_SORT = SortName']	SortName is either the linguistic sort sequence or BINARY. If you omit this parameter, then the default sort sequence for the session is used. Append to the SortName the suffix -ai for accent-insensitive sorting or -ci for case-insensitive sorting.

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 Name > 'Gaberd';
< Gardiner >
1 row 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';
Session altered.
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 range index on columns:
    NLSSORT(NAME,'NLS_SORT = XDanish')
  1 index found.
1 index found on 1 table.