Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
Purpose
NLSSORT
returns the string of bytes used to sort char
.
Both char
and 'nlsparam'
can be any of the datatypes CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
. The string returned is of RAW
datatype.
The value of 'nlsparam'
can have the form
'NLS_SORT = sort'
where sort
is a linguistic sort sequence or BINARY
. If you omit 'nlsparam'
, then this function uses the default sort sequence for your session. If you specify BINARY
, then this function returns char
.
If you specify 'nlsparam'
, then you can append to the linguistic sort name the suffix _ai
to request an accent-insensitive sort or _ci
to request a case-insensitive sort. Please refer to Oracle Database Globalization Support Guide for more information on accent- and case-insensitive sorting.
This function does not support CLOB
data directly. However, CLOB
s can be passed in as arguments through implicit data conversion.
See Also:
"Datatype Comparison Rules" for more information.Examples
This function can be used to specify sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of a string. The following example creates a test table containing two values and shows how the values returned can be ordered by the NLSSORT
function:
CREATE TABLE test (name VARCHAR2(15)); INSERT INTO test VALUES ('Gaardiner'); INSERT INTO test VALUES ('Gaberd'); INSERT INTO test VALUES ('Gaasten'); SELECT * FROM test ORDER BY name; NAME --------------- Gaardiner Gaasten Gaberd SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = XDanish'); NAME --------------- Gaberd Gaardiner Gaasten
The following example shows how to use the NLSSORT
function in comparison operations:
SELECT * FROM test WHERE name > 'Gaberd'; no rows selected SELECT * FROM test WHERE NLSSORT(name, 'NLS_SORT = XDanish') > NLSSORT('Gaberd', 'NLS_SORT = XDanish'); NAME --------------- Gaardiner Gaasten
If you frequently use NLSSORT
in comparison operations with the same linguistic sort sequence, then consider this more efficient alternative: Set the NLS_COMP
parameter (either for the database or for the current session) to LINGUISTIC
, and set the NLS_SORT
parameter for the session to the desired sort sequence. Oracle Database will use that sort sequence by default for all sorting and comparison operations during the current session:
ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; ALTER SESSION SET NLS_SORT = 'XDanish'; SELECT * FROM test WHERE name > 'Gaberd'; NAME --------------- Gaardiner Gaasten
See Also:
Oracle Database Globalization Support Guide for information on sort sequences