Locale names for SQL and XQuery
When used in an SQL or XQuery statement, a locale name consists of one or more ordered pieces of information. The CLDR version prefix, language code, script code, country/region code, and variant codes must be separated with the underscore character (_). Keywords are introduced with the commercial at symbol (@) and multiple keywords are separated by the semicolon character (;).
- CLDR version prefix
The behaviors associated with a locale are defined in the Common Locale Data Repository (CLDR). CLDR is a set of standards for locale data, including the formatting of dates, times, time zones, numbers, and currency values; the sorting of text; the translations for country, language, and territory names; and other variables. For more information about CLDR, see http://cldr.unicode.org.
As international standards evolve, the behavior associated with a locale may change. Future versions of Db2® may implement newer versions of the CLDR, which might have unexpected effects on applications or database objects. To avoid unexpected changes to locale behavior, the locale name may be prefixed with a CLDR version listed in Table 1 to force future versions of Db2 to maintain a particular CLDR version behavior. If the CLDR version prefix is specified, then at least one locale code must also be specified.
Table 1. Supported CLDR versions CLDR version prefix Equivalent prefixes for compatibility with previous versions of Db2 First introduced in Db2 version CLDR12_ - CLDR 1.2:
- U400
Version 9.1 Fix Pack 4 CLDR15_ CLDR 1.5: Version 9.5 Fix Pack 1 CLDR181_ none 10.1 Note: If the locale is specified as part of a database object (such as an index or constraint), use the CLDR version prefix to avoid unexpected changes in behavior when upgrading to future releases of the Db2 database.- Language code
- The languages are specified by using a two- or three-letter lowercase code for a particular language. For example, Spanish is es, English is en, and French is fr. The two-letter language code uses the ISO 639 standard. For more information about the ISO 639 standard, see the ISO 639-2 Registration Authority web page from the Library of Congress at http://www.loc.gov/standards/iso639-2/.
- Script code
- The optional four-letter script code follows the language code. If specified, it should be a valid script code as listed on the Unicode ISO 15924 Registry. For more information about the ISO 15924 codes, see the ISO 15924 Code List web page at http://www.unicode.org/iso15924/iso15924-codes.html.
- Country or region code
- There are often different language conventions within the same language. For example, Spanish is spoken in many countries in Central and South America but the currencies are different in each country. To allow for these differences among specific geographical, political, or cultural regions, locales are specified by two-letter, uppercase codes. For example, ESrepresents Spain and MXrepresents Mexico. The two letter country or region code uses the ISO 3166 standard. For more information about the ISO 3166 standard, see the ISO 3166 code lists from the International Organization for Standardization at http://www.iso.org/iso/country_codes/country_codes.
- Keywords
- The final element of a locale is an optional list of keywords
together with their values. Keywords must be unique. Their order is
not significant. Unknown keywords are ignored. The handling of keywords
depends on the specific services that use them. Currently, the following
keywords are recognized:
- calendar
- A calendar specifier such as gregorian, arabic, chinese, civil-arabic, hebrew, japanese, or thai-buddhist.
- collation
- A collation specifier such as phonebook, pinyin, traditional, stroke, direct, or posix.
- currency
- Any standard three-letter currency code, such as USD or JPY.
Examples
| Locale name | Language | Script | Country/region | Keywords | Definition |
|---|---|---|---|---|---|
| en_US | en | US | English, United States of America | ||
| CLDR181_en | en | English, fixed to CLDR version 1.8.1 behavior | |||
| en_IE@currency=IEP | en | IE | currency= IEP | English, Ireland with Irish Pound | |
| eo | eo | Esperanto | |||
| fr@collation=phonebook;calendar=islamic-civil | fr | collation=phonebook calendar=islamic-civil | French (Calendar=Islamic-Civil Calendar, Collation=Phonebook Order) | ||
| sr_Latn_YU@currency=USD | sr | Latn | YU | currency=USD | Serbian (Latin, Yugoslavia, Currency=US Dollar) |