The NLS_SORT parameter controls how Oracle Database sorts character data by determining the collation sequence used for comparison operations. This parameter directly affects ORDER BY clauses, comparison operators, and functions like MIN, MAX, and GROUP BY operations.

The parameter works in conjunction with NLS_COMP, which must be set to LINGUISTIC for most NLS_SORT values to take effect. When NLS_COMP is set to BINARY, character comparisons are performed using binary values regardless of the NLS_SORT setting.

For detailed explanation of NLS_COMP parameter values, see NLS_COMP Parameter
For indexing strategies with this parameter, see NLSSORT Function Indexes

This table and sample data is used in the examples below:

CREATE TABLE table_nls_sort
(
    pkid NUMBER(1,0) NOT NULL
,   code VARCHAR(2) NOT NULL
);

INSERT INTO table_nls_sort (pkid, code) VALUES (1, 'aé');
INSERT INTO table_nls_sort (pkid, code) VALUES (2, 'ae');
INSERT INTO table_nls_sort (pkid, code) VALUES (3, 'af');
INSERT INTO table_nls_sort (pkid, code) VALUES (4, 'ad');
INSERT INTO table_nls_sort (pkid, code) VALUES (5, 'aÉ');
INSERT INTO table_nls_sort (pkid, code) VALUES (6, 'aE');
INSERT INTO table_nls_sort (pkid, code) VALUES (7, 'aF');
INSERT INTO table_nls_sort (pkid, code) VALUES (8, 'aD');

COMMIT;

ALTER SESSION SET NLS_SORT = BINARY;
ALTER SESSION SET NLS_COMP = LINGUISTIC;

SELECT pkid, code FROM table_nls_sort ORDER BY code;

Query result:

pkid code
---- ----
8    aD
6    aE
7    aF
4    ad
2    ae
3    af
5    aÉ
1    aé

Note: In the ASCII standard, uppercase characters appear before lowercase characters. This is why 'aD', 'aE', and 'aF' appear before their lowercase counterparts in the BINARY sort result.

The NLS_SORT parameter can be modified at the session level using the ALTER SESSION statement. This change affects only the current database session and does not impact other users or sessions. The parameter accepts either predefined binary sort types or linguistic definitions for specific languages and territories.

ALTER SESSION SET NLS_SORT = { BINARY | BINARY_AI | BINARY_CI | linguistic_definition };

The three main binary sort options provide different levels of sensitivity:

• BINARY:
  Sorting will be accent-sensitive and case-sensitive. This is the fastest sorting method as it uses the binary values of characters directly.
  

  ALTER SESSION SET NLS_SORT=BINARY; -- Sorting is based on the binary values of characters
  

• BINARY_AI:
  Sorting will be accent-insensitive and case-insensitive. Characters with different accents (é, è, ê) are treated as equivalent, and uppercase/lowercase differences are ignored.
  

  ALTER SESSION SET NLS_SORT=BINARY_AI;
  

• BINARY_CI:
  Sorting will be case-insensitive but accent-sensitive. Uppercase and lowercase letters are treated as equivalent, but accented characters are still distinguished from their unaccented counterparts.
  

  ALTER SESSION SET NLS_SORT=BINARY_CI;
  

For linguistic definitions, you can specify language-specific sorting rules such as FRENCH, GERMAN, or SPANISH. These linguistic sorts follow the collation rules specific to each language and culture.

Oracle provides the V$NLS_VALID_VALUES system view to discover all supported NLS_SORT values in your database instance. This view is particularly useful when working with multilingual applications or when you need to verify which linguistic definitions are available.

The V$NLS_VALID_VALUES view contains information about valid parameter values for various NLS parameters, including sort definitions, and indicates whether specific values are deprecated.

SELECT PARAMETER, VALUE, ISDEPRECATED FROM v$nls_valid_values WHERE parameter = 'SORT';

Query result:

| PARAMETER | VALUE     | ISDEPRECATED |
+-----------+-----------+--------------|
| SORT      | BINARY    | FALSE        |
| SORT      | ASCII7    | FALSE        |
| SORT      | ARABIC    | FALSE        |
| SORT      | BULGARIAN | FALSE        |
| ...       | ...       | ...          |

The ISDEPRECATED column indicates whether a particular sort value is still supported.

The BINARY sort option uses the numeric values of characters in the database character set for sorting. This method treats each character distinctly based on its binary representation, making it both accent-sensitive and case-sensitive. BINARY sorting is the fastest method because it requires no linguistic processing.

The NLSSORT function can be used to visualize the sort keys that Oracle generates for each character string. These hexadecimal values represent how Oracle internally compares the strings during sorting operations.

Example 1:

SELECT pkid, code, NLSSORT(code, 'NLS_SORT=BINARY') AS code_binary FROM table_nls_sort ORDER BY code_binary, code;

Query result:

pkid code code_binary
---- ---- --------------
8    aD   614400
6    aE   614500
7    aF   614600
4    ad   616400
2    ae   616500
3    af   616600
5    aÉ   61C900
1    aé   61E900

Example 2:

SELECT DISTINCT NLSSORT(code, 'NLS_SORT=BINARY') AS code_binary FROM table_nls_sort;

Query result:

code_binary
-----------
614400
614500
614600
616400
616500
616600
61C900
61E900

Note that each character combination produces a unique sort key, demonstrating that BINARY sort distinguishes between all variations of case and accents.

The BINARY_CI (Case Insensitive) sort option ignores differences between uppercase and lowercase letters while still maintaining sensitivity to accented characters. This means that 'A' and 'a' are treated as equivalent, but 'a' and 'á' are still considered different characters.

This sorting method is useful in applications where case variations should not affect sort order, but accent distinctions remain important for proper linguistic representation.

Example 1:

SELECT pkid, code, NLSSORT(code, 'NLS_SORT=BINARY_CI') AS code_binary_ci FROM table_nls_sort ORDER BY code_binary_ci, code;

Query result:

pkid code code_binary_ci
---- ---- --------------
8    aD   616400
4    ad   616400
6    aE   616500
2    ae   616500
7    aF   616600
3    af   616600
5    aÉ   61E900
1    aé   61E900

Example 2:

SELECT DISTINCT NLSSORT(code, 'NLS_SORT=BINARY_CI') AS code_binary_ci FROM table_nls_sort;

Query result:

code_binary_ci
--------------
616400
616500
616600
61E900

Note that uppercase and lowercase versions of the same letter now produce identical sort keys (e.g., 'aD' and 'ad' both have 616400), while accented characters still generate distinct values.

The BINARY_AI (Accent Insensitive) sort option provides the most flexible character comparison by ignoring both case differences and accent variations. Under this sorting method, characters like 'a', 'A', 'á', 'à', 'â', and 'ä' are all treated as equivalent for sorting purposes.

This sorting approach is particularly valuable in search applications, user interfaces where exact accent matching is not critical, or when dealing with data that may have inconsistent accent usage. However, it should be used carefully in applications where linguistic accuracy is important.

Example 1:

SELECT pkid, code, NLSSORT(code, 'NLS_SORT=BINARY_AI') AS code_binary_ai FROM table_nls_sort ORDER BY code_binary_ai, code;

Query result:

pkid code code_binary_ai
---- ---- --------------
8    aD   616400
4    ad   616400
6    aE   616500
2    ae   616500
5    aÉ   616500
1    aé   616500
7    aF   616600
3    af   616600

Example 2:

SELECT DISTINCT NLSSORT(code, 'NLS_SORT=BINARY_AI') AS code_binary_ai FROM table_nls_sort;

Query result:

code_binary_ai
--------------
616400
616500
616600

Note that all variations of 'ae' ('ae', 'aE', 'aÉ', 'aé') now produce the same sort key (616500), demonstrating that both case and accent differences are ignored in the comparison.