Oracle NLSSORT() Function

Oracle NLSSORT() is a built-in function that returns the sort key for a given string, based on the specified sorting rules, whether they are explicit or implicit.

The sort key is a byte string used to sort the string based on the sorting rules. The property of collation keys is that the ordering of any two such keys generated for a given collation is the same when compared based on their binary order as the ordering of the source character values when compared based on the given collation.

Oracle NLSSORT() Syntax

Here is the syntax for the Oracle NLSSORT() function:

NLSSORT(str [, 'nlsparam' ])

Parameters

str

Required. It can be of any data type in CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

'nlsparam'

Optional. You can use this parameter in the form of 'NLS_SORT = sort', where sort is the name of the sorting rule. Sorting rules handle the requirements of special languages for case conversion. If you omit this parameter, the sorting rule is determined by the function.

Return Value

The Oracle NLSSORT() function returns the sort key for the given string, based on the specified sorting rules, whether they are explicit or implicit.

If any of the arguments is NULL, NLSSORT() will return NULL.

Oracle NLSSORT() Examples

Here are several examples that demonstrate the usage of the Oracle NLSSORT() function.

Basic Usage

SELECT
    NLSSORT('HELLO'),
    NLSSORT('WORLD')
FROM dual;

Output:

NLSSORT('HELLO')    NLSSORT('WORLD')
___________________ ___________________
48454C4C4F00        574F524C4400

Sorting Rules

The Oracle NLSSORT() function allows you to specify sorting rules to handle the requirements of special languages.

SELECT
    NLSSORT('HELLO'),
    NLSSORT('WORLD', 'NLS_SORT = XDanish')
FROM dual;

Output:

NLSSORT('HELLO')    NLSSORT('WORLD','NLS_SORT=XDANISH')
___________________ ______________________________________
48454C4C4F00        7A5A644B2300010101010100

Sorting

You can use the NLSSORT() function in a SELECT statement to sort based on the specified sorting rules.

First, simulate a dataset using the UNION operation:

SELECT 'Gaardiner' name FROM DUAL
UNION ALL
SELECT 'Gaberd' name FROM DUAL
UNION ALL
SELECT 'Gaasten' name FROM dual;

If you need to sort by name in ascending order, you can use this statement:

SELECT *
FROM (
    SELECT 'Gaardiner' name
    FROM DUAL
    UNION ALL
    SELECT 'Gaberd' name
    FROM DUAL
    UNION ALL
    SELECT 'Gaasten' name
    FROM DUAL
  ) t
ORDER BY name;

Output:

NAME
____________
Gaardiner
Gaasten
Gaberd

Now, let’s see what happens when we specify the sorting rules with 'NLS_SORT = XDanish':

SELECT *
FROM (
    SELECT 'Gaardiner' name
    FROM DUAL
    UNION ALL
    SELECT 'Gaberd' name
    FROM DUAL
    UNION ALL
    SELECT 'Gaasten' name
    FROM DUAL
  ) t
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

Output:

NAME
____________
Gaberd
Gaardiner
Gaasten

Here, we have a completely different order.

NULL Parameter

If any parameter is NULL, NLSSORT() will return NULL.

SET NULL 'NULL';
SELECT
    NLSSORT(NULL)
FROM dual;

Output:

NLSSORT(NULL)
________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle NLSSORT() is a built-in function that returns the sort key of a given string and the sorting rules explicitly or implicitly specified.