SET COLLATION statement
Use the SET COLLATION statement to specify a new collating order for the session, superseding the collation implied by the DB_LOCALE environment variable setting. SET NO COLLATION restores the default collation.
Syntax
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| locale | Name of a locale whose collating order is to be used in this session | Must be the name of a locale that the database server can access | Quoted String |
Usage
The SET COLLATION statement is an extension to the ANSI/ISO standard for SQL. You can use this statement with .
As the HCL OneDB™ GLS User's Guide explains, the database server uses locale files to specify the character set, the collating order, and other conventions of some natural language to display and manipulate character strings and other data values. The collating order of the database locale is the sequential order in which the database server sorts character strings.
If you set no value for DB_LOCALE, the default locale, based on United States English, is en_us.8859-1 for UNIX™, or Code Page 1252 for Windows™ systems. Otherwise, the database server uses the DB_LOCALE setting as its locale. The SET COLLATION statement overrides the collating order of DB_LOCALE at runtime for all database servers previously accessed in the same session.
The new collating order remains in effect for the rest of the session, or until you issue another SET COLLATION statement. Other sessions are not affected, but database objects that you created with a non-default collation use whatever collating order was in effect at their time of their creation.
By default, the collating order is the code-set order, but some locales also support a locale-specific order. In most contexts, only NCHAR and NVARCHAR data values can be sorted according to a locale-specific collating order.