NLSSORT() Function in Oracle

In Oracle, the NLSSORT() function returns a collation key for a given character value and an explicitly or implicitly specified collation.

The function can be useful for performing comparisons against string values based on a specified collation.

Syntax

The syntax goes like this:

NLSSORT(char [, 'nlsparam' ])

Where both char and ‘nlsparam‘ can be any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

The 'nlsparam' argument can have the following form:

'NLS_SORT = collation'

Where collation is the name of a linguistic collation or BINARY. 

NLSSORT() uses the specified collation to generate the collation key. If you omit ‘nlsparam‘, then the function uses the derived collation of the argument char.

Example

Here’s an example of a return value of the function:

SELECT 
    NLSSORT('Chess') AS Result
FROM DUAL;

Result:

         RESULT 
_______________ 
436865737300   

Specify a Collation

In this example, I specify a collation:

SELECT 
    NLSSORT('Schach', 'NLS_SORT=XGERMAN') AS Result
FROM DUAL;

Result:

                         RESULT 
_______________________________ 
691E37141E370003010101010100   

Comparing Two Strings

Here’s an example of how the NSSORT() function can be used to compare two strings based on their language rules:

DECLARE 
    v1 NVARCHAR2(10) := 'Schach';
    v2 NVARCHAR2(10) := 'Schabsel';
BEGIN
    IF NLSSORT(v1, 'NLS_SORT=XGERMAN') > NLSSORT(v2, 'NLS_SORT=XGERMAN') THEN
        DBMS_OUTPUT.PUT_LINE( 'Yes' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( 'No' );
    END IF;
END;

Result:

Yes

Null Values

Passing null returns null:

SELECT
    NLSSORT(null, 'NLS_SORT = XAZERBAIJANI') AS r1,
    NLSSORT('fasilə', null) AS r2,
    NLSSORT(null, null) AS r3
FROM DUAL;

Result:

     R1      R2      R3 
_______ _______ _______ 
null    null    null   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling NLSSORT() without passing any arguments returns an error:

SELECT NLSSORT()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLSSORT()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing too many arguments results in an error:

SELECT NLSSORT('coffee', 'NLS_SORT = XSPANISH', 'time')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NLSSORT('coffee', 'NLS_SORT = XSPANISH', 'time')
FROM DUAL
Error at Command Line : 1 Column : 49
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action:

More Information

See the Oracle SQL Language Reference for more information about the NLSSORT() function.