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:
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook