Oracle NLSSORT function

Last update on May 30 2022 10:59:23 (UTC/GMT +8 hours)

Description

The NLSSORT function is used to replace a character string with the equivalent sort string used by the linguistic sort mechanism. In the case of a binary sort, the sort string is the same as the input string. In linguistic sort, each character in a string is replaced by some other binary values.

Syntax:

NLSSORT(string1 [, 'nlsparam' ])

Parameters:

Name	Description	Data Type
string1	equivalent sort string used by the linguistic sort mechanism.	CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
nlsparam	The value of 'nlsparam' can have this form : 'NLS_SORT = sort' where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. These requirements can result in a return value of a different length than the char. If you omit 'nlsparam', then this function uses the default sort sequence for your session.	CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Examples: Oracle NLSSORT function

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 mytable (name VARCHAR2(15));

Now insert 3 rows into the mytable. Here is the code.

INSERT INTO mytable VALUES ('Jaanifer');
INSERT INTO mytable VALUES ('Jamtina');
INSERT INTO mytable VALUES ('Jaasfield');

Now show the data of the table in ascending order of a name. Here is the code.
SELECT *
  FROM mytable
  ORDER BY name;
  
 NAME
------------
Jaanifer
Jaasfield
Jamtina

SELECT *
FROM mytable
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

Sample Output:

NAME
-----------
Jamtina
Jaanifer
Jaasfield

The following example shows how to use the NLSSORT function in comparison operations:

SELECT *
FROM mytable
WHERE name > 'Jamtina'
ORDER BY name;

Sample Output:

 no rows selected

SELECT *
FROM mytable
WHERE NLSSORT(name, 'NLS_SORT = XDanish') > 
NLSSORT('Jamtina', 'NLS_SORT = XDanish')
ORDER BY name;

Sample Output:

NAME
---------------
Jaanifer
Jaasfield

Previous: NLS_UPPER
Next: REGEXP_REPLACE