NLSSORT returns a collation key for the character value
char and an explicitly or implicitly specified collation. A collation key is a string of bytes used to sort
char according to the specified collation. The property of the collation keys is that mutual ordering of two such keys generated for the given collation when compared according to their binary order is the same as mutual ordering of the source character values when compared according to the given collation.
char and '
nlsparam' can be any of the data types
The value of '
nlsparam' must have the form
'NLS_SORT = collation'
collation is the name of a linguistic collation or
NLSSORT uses the specified collation to generate the collation key. If you omit '
nlsparam', then this function uses the derived collation of the argument
char. If you specify
BINARY, then this function returns the
char value itself cast to
RAW and possibly truncated as described below.
If you specify '
nlsparam', then you can append to the linguistic collation name the suffix
_ai to request an accent-insensitive collation or
_ci to request a case-insensitive collation. Refer to Oracle Database Globalization Support Guide for more information on accent- and case-insensitive sorting. Using accent-insensitive or case-insensitive collations with the
BY query clause is not recommended as it leads to a nondeterministic sort order.
The returned collation key is of
RAW data type. The length of the collation key resulting from a given
char value for a given collation may exceed the maximum length of the
RAW value returned by
NLSSORT. In this case, the behavior of
NLSSORT depends on the value of the initialization parameter
EXTENDED, then the maximum length of the return value is 32767 bytes. If the collation key exceeds this limit, then the function fails with the error "ORA-12742: unable to create the collation key". This error may also be reported for short input strings if they contain a high percentage of Unicode characters with very high decomposition ratios.
Oracle Database Globalization Support Guide for details of when the
ORA-12742 error is reported and how to prevent application availability issues that the error could cause
STANDARD, then the maximum length of the return value is 2000 bytes. If the value to be returned exceeds the limit, then
NLSSORT calculates the collation key for a maximum prefix, or initial substring, of
char so that the calculated result does not exceed the maximum length. For monolingual collations, for example
FRENCH, the prefix length is typically 1000 characters. For multilingual collations, for example
GENERIC_M, the prefix is typically 500 characters. For Unicode Collation Algorithm (UCA) collations, for example
UCA0610_DUCET, the prefix is typically 285 characters. The exact length may be lower or higher depending on the collation and the characters contained in
The behavior when
STANDARD implies that two character values whose collation keys (
NLSSORT results) are compared to find the linguistic ordering are considered equal if they do not differ in the prefix even though they may differ at some further character position. Because the
NLSSORT function is used implicitly to find linguistic ordering for comparison conditions, the
BETWEEN condition, the
COUNT(DISTINCT), those operations may return results that are only approximate for long character values. If you want guarantee that the results of those operations are exact, then migrate your database to use
Refer to "Extended Data Types" for more information on the
MAX_STRING_SIZE initialization parameter.
This function does not support
CLOB data directly. However,
CLOBs can be passed in as arguments through implicit data conversion.
"Data Type Comparison Rules" for more information.
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules for
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
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' ORDER BY name; no rows selected SELECT * FROM test WHERE NLSSORT(name, 'NLS_SORT = XDanish') > NLSSORT('Gaberd', 'NLS_SORT = XDanish') ORDER BY name; 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' ORDER BY name; NAME --------------- Gaardiner Gaasten
Oracle Database Globalization Support Guide for information on sort sequences