About the NLS_SORT Parameter

Specifies: Linguistic sort order (collating sequence) for queries that have the ORDER BY clause.

Acceptable Values:

BINARY

Sort order is based on the binary sequence order of either the database character set or the national character set, depending on the data type.
Any linguistic sort name that Oracle supports

Sort order is based on the order of the specified linguistic sort name. The linguistic sort name is usually the same as the language name, but not always. For a list of supported linguistic sort names, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANGUAGE, described in "About the NLS_LANGUAGE Parameter".

The following example shows how two different NLS_SORT settings affect the displayed result of the same query. The settings are BINARY and Traditional Spanish (SPANISH_M). Traditional Spanish treats ch, ll, and ñ as letters that follow c, l, and n, respectively.

To try this example in SQL Developer, enter the statements and queries in the SQL Worksheet. For information about the SQL Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

NLS_SORT Affects Linguistic Sort Order

Create table for Spanish words:
```
CREATE TABLE temp (name VARCHAR2(15));
```

Populate table with some Spanish words:

INSERT INTO temp (name) VALUES ('laguna');
INSERT INTO temp (name) VALUES ('llama');
INSERT INTO temp (name) VALUES ('loco');

Note the current value of NLS_SORT.

For instructions, see "Viewing NLS Parameter Values".
If the valueof NLS_SORT in step 3 is not BINARY, change it:
```
ALTER SESSION SET NLS_SORT=BINARY;
```

Run this query:

SELECT * FROM temp ORDER BY name;

Result:

NAME
---------------
laguna
llama
loco

Change the value of NLS_SORT to SPANISH_M (Traditional Spanish):
```
ALTER SESSION SET NLS_SORT=SPANISH_M;
```
Repeat the query from step 5.

Result:
```
NAME
---------------
laguna
loco
llama
```
Drop the table:
```
DROP TABLE temp;
```
Set NLS_SORT to the value that it had at step 3.

Case-Insensitive and Accent-Insensitive Sorts

Operations inside Oracle Database are sensitive to the case and the accents of the characters. To perform a case-insensitive sort, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or XGERMAN_CI). To perform a sort that is both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter (for example, BINARY_AI or FRENCH_M_AI).

About the NLS_SORT Parameter

Related Topics