About the NLS_COMP Parameter

Specifies: Character comparison behavior of SQL operations.

Acceptable Values:

BINARY

SQL compares the binary codes of characters. One character is greater than another if it has a higher binary code.
LINGUISTIC

SQL performs a linguistic comparison based on the value of the NLS_SORT parameter, described in "About the NLS_SORT Parameter".
ANSI

This value is provided only for backward compatibility.

Default Value: BINARY

The following example shows that the result of a query can depend on the NLS_COMP setting.

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_COMP Affects SQL Character Comparison

Note the current values of NLS_SORT and NLS_COMP.

For instructions, see "Viewing NLS Parameter Values".
If the values of NLS_SORT and NLS_COMP in step 1 are not SPANISH_M (Traditional Spanish) and BINARY, respectively, change them:
```
ALTER SESSION SET NLS_SORT=SPANISH_M NLS_COMP=BINARY;
```

*Run this query:

SELECT LAST_NAME FROM EMPLOYEES
WHERE LAST_NAME LIKE 'C%';

Result:

LAST_NAME                 
------------------------- 
Cabrio                    
Cambrault                 
Cambrault                 
Chen                      
Chung                     
Colmenares                
 
6 rows selected

Change the value of NLS_COMP to LINGUISTIC:
```
ALTER SESSION SET NLS_COMP=LINGUISTIC;
```

Repeat the query from step 3.

Result:

LAST_NAME                 
------------------------- 
Cabrio                    
Cambrault                 
Cambrault                 
Colmenares                
 
4 rows selected

This time, Chen and Chung are not returned because Traditional Spanish treats ch as a single character that follows c.

Set NLS_SORT and NLS_COMP to the values that they had in step 1.

About the NLS_COMP Parameter

Related Topics