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.
See my articles more often when you search Google.
