Oracle NLSSORT function

Last update on August 05 2024 14:17:30 (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. This is especially useful for sorting and comparing strings according to linguistic rules rather than binary values. 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.

Uses of Oracle NLSSORT Function

Linguistic Sorting: Sorts character strings based on linguistic conventions rather than binary values.

Linguistic Comparisons: Compares strings according to language-specific rules.

Multilingual Data Handling: Supports sorting and comparison operations in multilingual databases.

Custom Sort Orders: Enables custom sort sequences by specifying different linguistic sort parameters.

Enhanced Query Performance: Improves performance of sorting and comparison operations for linguistic data.

Standardizing Sorting Behavior: Ensures consistent sorting behavior across different sessions and environments.

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