Using Linguistic Sort and Search
Establishing a Globalization Support Environment
6-22 Oracle Database 2 Day Developer's Guide
Using Linguistic Sort and Search
Different languages have their own sorting rules. Some languages are collated
according to the letter sequence in the alphabet, some according to the number of
stroke counts in the letter, and some are ordered by the pronunciation of the words.
Treatment of letter accents also differs among languages. For example, in Danish, Æ is
sorted after Z, while Y and Ü are considered to be variants of the same letter.
You can define how to sort data by using linguistic sort parameters. The basic
linguistic definition treats strings as sequences of independent characters.
Using the NLS_SORT Parameter
The
NLS_SORT
parameter specifies the collating (linguistic sort) sequence for
ORDER
BY
queries. It overrides the default
NLS_SORT
value that is derived from the
NLS_
LANGUAGE
parameter. The value of
NLS_SORT
can be
BINARY
or any valid linguistic
sort name:
NLS_SORT
=
BINARY
|
sort_name
If the value is
BINARY
, then the collating sequence is based on the numeric code of the
characters in the underlying encoding scheme. Depending on the data type, this will
either be in the binary sequence order of the database character set or the national
character set. If the value is a named linguistic sort, sorting is based on the order of the
defined sort. Most, but not all, languages supported by the
NLS_LANGUAGE
parameter
also support a linguistic sort with the same name.
To set the NLS_SORT parameter:
You can change the
NLS_SORT
parameter value and see the effect in the display of
results from a query. The following examples show the effect of setting
NLS_SORT
first
to Binary and then to Spanish (
SPANISH_M
). Spain traditionally treats ch, ll, and ñ as
letters of their own, ordered after c, l, and n, respectively.
1.
In SQL Developer, make a note of the current collating 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 collating format is
listed after
NLS_SORT
.
2.
Set
NLS_SORT
to binary.
ALTER SESSION SET NLS_SORT=BINARY;
3.
Enter a
SELECT
statement with an
ORDER BY
clause, to check the output after the
change.
SELECT last_name FROM employees
WHERE last_name LIKE 'C%'
ORDER BY last_name;
The output from the example should be similar to the following.
LAST_NAME
-------------------------
See Also:
Oracle Database Globalization Support Guide for a complete
discussion of linguistic sort and string searching
