oracle database nationality collation NLS - ghdrako/doc_snipets GitHub Wiki
- https://learnsql.com/blog/collations-in-oracle/
- https://github.com/ghdrako/doc_snipets/wiki/oracle-nls-params-date-timestamp-format
| Option Name | Description |
|---|---|
| NLS_LANG | The current language, territory, and database character set, which are determined by session-wide globalization parameters. |
| NLS_LANGUAGE | The current language for the session. |
| NLS_SORT | The sequence of character values used when sorting or comparing text. |
NLS_SORT- we can specify the sort method (binary or linguistic) for both SQL WHERE clause operations and NLSSORT function operations.
check the current NLS settings
SELECT * FROM v$NLS_PARAMETERS;
Oracle provides two main types of collation:
- binary and
- linguistic.
Binary Collation
With a binary sort characters are sorted in the order of their binary representation. In case of different languages, a binary collation doesn't produce reasonable results. It’s a default collation of the database session for ORDER BY and BETWEEN (and other operations that support linguistic sort) and is set as binary in NLS_COMP variable.
Linguistic Collation
A linguistic sort operates by replacing characters with other binary values that reflect the character’s proper linguistic order so that a sort returns the desired result. You can specify the collation behavior by setting NLS_COMP = linguistic.
There are three types of linguistic collation:
- Monolingual Collation
- Multilingual Collation
- Unicode Collation Algorithm (UCA)
List Collations
Get a list with the valid NLS_SORT values
SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'SORT';
Set NLS Parameters
- as initialization parameters on the instance/server:
ALTER SYSTEM SET V$NLS_PARAMETER = 'XXX' scope = both;
Example
ALTER SYSTEM SET NLS_SORT='RUSSIAN' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_COMP='LINUGUISTIC' SCOPE=SPFILE;
- as environment variables on the client:
% setenv NLS_SORT FRENCH
- for a session:
ALTER SESSION SET V$NLS_PARAMETER = = 'XXX'
If you don’t want to globally affect the instance, you can use the NLSSORT() function to set the NLS_SORT for the scope of a specific query. For example:
SELECT *FROM testORDER BY NLSSORT(name, 'NLS_SORT=german');