Setting the NLS_COMP Parameter
Establishing a Globalization Support Environment
6-24 Oracle Database 2 Day Developer's Guide
comparison. When
NLS_COMP
is set to
LINGUISTIC
, SQL performs a linguistic
comparison based on the value of the
NLS_SORT
parameter.
To set the NLS_COMP parameter:
You can change the
NLS_COMP
parameter value and see the effect in the display of
results from a query. The following examples show the effect of performing a binary
comparison followed by a Spanish linguistic sensitive comparison against the
employee names.
1.
In SQL Developer, make a note of the current comparison operators format in
which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database,
and then National Language Support Parameters. In the Select Connection dialog
box, select
hr_conn
from the list of connections. The current comparison
operators format is listed after
NLS_COMP
.
2.
Set
NLS_SORT
to Spanish and
NLS_COMP
to
BINARY
.
ALTER SESSION SET NLS_SORT=spanish_m NLS_COMP=binary;
3.
Enter a
SELECT
statement to return employees whose last name starts with
C
.
SELECT last_name FROM employees
WHERE last_name LIKE 'C%';
The output from the example should be similar to the following.
LAST_NAME
-------------------------
Cabrio
Cambrault
Cambrault
Chen
Chung
Colmenares
6 rows selected
4.
Set
NLS_COMP
to
LINGUISTIC
.
ALTER SESSION SET NLS_COMP=linguistic;
5.
Enter the same
SELECT
statement, to check the output after the change.
SELECT last_name FROM employees
WHERE last_name LIKE 'C%';
The output from the example should be similar to the following. Note that two
fewer rows are returned this time. Chen and Chung are not returned because in
Spanish
ch
is treated as a separate character that follows
c
, so
ch
is excluded
when a Spanish linguistic-sensitive comparison is performed.
LAST_NAME
-------------------------
Cabrio
Cambrault
Cambrault
Colmenares
4 rows selected
