By default Oracle sorts and compares strings by the binary values of their characters, which puts every uppercase letter before every lowercase one: van der Berg ends up after Zhang. NLSSORT returns a sort key for a string under a linguistic sort, such as case-insensitive or accent-insensitive, so ORDER BY and comparisons follow that rule instead.
Code for This Guide
The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
nlssort(char [, 'nls_sort = sort_name'])
NLSSORT returns a RAW value whose byte order matches the order of the named sort. Without the second argument, it uses the session's NLS_SORT. Common sort names:
| Sort | Effect |
|---|---|
| BINARY | Byte order: uppercase before lowercase, accents after plain letters |
| BINARY_CI | Case-insensitive |
| BINARY_AI | Accent- and case-insensitive |
| A language sort such as GERMAN or FRENCH | That language's alphabetical rules; append _CI or _AI for insensitive variants |
Sort Without Regard to Case
The first query shows each name with its BINARY_CI sort key and sorts by the name itself; the second sorts by the key.
Example:
select last_name, nlssort(last_name, 'NLS_SORT = BINARY_CI') as sort_key
from employees
where last_name in ('Zhang', 'van der Berg', 'Al Mansoori', 'Aziz', 'Okafor')
order by last_name;
select last_name
from employees
where last_name in ('Zhang', 'van der Berg', 'Al Mansoori', 'Aziz', 'Okafor')
order by nlssort(last_name, 'NLS_SORT = BINARY_CI');Output:
LAST_NAME SORT_KEY _______________ _____________________________ Al Mansoori 616C206D616E736F6F726900 Aziz 617A697A00 Okafor 6F6B61666F7200 Okafor 6F6B61666F7200 Zhang 7A68616E6700 van der Berg 76616E20646572206265726700 6 rows selected. LAST_NAME _______________ Al Mansoori Aziz Okafor Okafor van der Berg Zhang 6 rows selected.
The keys are the names in lowercase bytes followed by a zero byte, so every name sorts as if it were lowercase. In the binary order of the first query, van der Berg comes after Zhang; sorted by NLSSORT, it takes its place among the V names.
Compare Without Regard to Case or Accents
Comparing two NLSSORT keys compares the strings under the sort's rules. BINARY_CI ignores case, and BINARY_AI ignores accents as well.
Example:
-- a case-insensitive comparison with NLSSORT
select employee_id, last_name
from employees
where nlssort(last_name, 'NLS_SORT = BINARY_CI') = nlssort('VAN DER BERG', 'NLS_SORT = BINARY_CI');
-- accent-insensitive as well with BINARY_AI
select count(*) as muller_matches
from customers
where nlssort(last_name, 'NLS_SORT = BINARY_AI') = nlssort('muller', 'NLS_SORT = BINARY_AI');Output:
EMPLOYEE_ID LAST_NAME
______________ _______________
135 van der Berg
MULLER_MATCHES
_________________
1VAN DER BERG finds van der Berg, and muller finds Müller.
Things to Know
- A function-based index on NLSSORT(column, 'NLS_SORT = BINARY_CI') lets case-insensitive searches use an index.
- Setting the session parameters NLS_SORT and NLS_COMP = LINGUISTIC makes ordinary comparisons and ORDER BY follow a linguistic sort without calling NLSSORT.
- Collations, set with COLLATE on a column or an expression, are the declarative alternative to NLSSORT.
Related Guides
Conclusion
NLSSORT returns a sort key that orders strings by a linguistic rule. Use it in ORDER BY to sort without regard to case or accents, compare two keys for insensitive matching, and index it for fast insensitive searches.
