Oracle NLSSORT function

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:

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