A collation decides how strings compare and sort: whether 'sao paulo' equals 'São Paulo', and whether van der Berg sorts among the V names. Oracle lets you set a collation on a column or on a single expression with the COLLATE operator, and three functions tell you which collation applies and translate collation names and numbers: COLLATION, NLS_COLLATION_ID, and NLS_COLLATION_NAME.
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:
collation(expr) nls_collation_id(collation_name) nls_collation_name(collation_id [, flag]) expr collate collation_name
| Function | Returns |
|---|---|
| COLLATION | The name of the collation that applies to an expression |
| NLS_COLLATION_ID | The number of a collation name |
| NLS_COLLATION_NAME | The name of a collation number |
Common collations are BINARY (byte order), BINARY_CI (case-insensitive), BINARY_AI (accent- and case-insensitive), the linguistic ones such as GERMAN_CI, and USING_NLS_COMP, which means "follow the session's NLS_COMP and NLS_SORT settings".
Inspect and Apply Collations
The first query shows the collation of a column, the collation of the same column after a COLLATE operator, and the number and name of BINARY_AI. The second compares with BINARY_AI to find São Paulo from plain lowercase text.
Example:
select collation(city) as column_collation,
collation(city collate binary_ci) as expression_collation,
nls_collation_id('BINARY_AI') as id,
nls_collation_name(nls_collation_id('BINARY_AI')) as name
from airports
where airport_code = 'DXB';
select city from airports where city collate binary_ai = 'sao paulo';Output:
COLUMN_COLLATION EXPRESSION_COLLATION ID NAME ___________________ _______________________ ________ ____________ USING_NLS_COMP BINARY_CI 81919 BINARY_AI CITY ____________ São Paulo
The city column has no collation of its own, so it reports USING_NLS_COMP. COLLATE BINARY_CI changes the collation of that one expression. With BINARY_AI, the comparison ignores both the accent on ã and the case.
Where Collations Come From
The collation of an expression is derived in this order:
- A COLLATE operator on the expression itself.
- The collation declared on the column, with COLLATE in CREATE TABLE or ALTER TABLE.
- The default collation of the table or schema.
- USING_NLS_COMP, which follows the session settings.
Column-level collations other than USING_NLS_COMP require the database parameter MAX_STRING_SIZE to be EXTENDED. The COLLATE operator in a query works without it, as in the example above.
Things to Know
- A collation affects comparisons, ORDER BY, GROUP BY, DISTINCT, and functions such as INSTR and REPLACE that compare characters.
- For an index to support a case-insensitive comparison, it must be built with the same collation, for example on the column with a BINARY_CI collation.
- NLSSORT gives the same effect for a single sort or comparison and works in any database.
Related Guides
Conclusion
COLLATION reports which collation applies to an expression, and NLS_COLLATION_ID and NLS_COLLATION_NAME convert between collation names and numbers. Apply BINARY_CI or BINARY_AI with the COLLATE operator to compare without regard to case or accents, or declare it on a column so every comparison follows it.
