Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

CREATE COLLATION

Statements

NAME

CREATE COLLATION: define a collation

SYNOPSIS

<collation definition> ::=
    CREATE COLLATION <collation name> FOR SQL_TEXT
    FROM <existing collation name> [ <pad characteristic> ]

<pad characteristic> ::= NO PAD | PAD SPACE

<collate clause> ::= COLLATE <collation name>

DESCRIPTION

A collation says how character strings compare. A column takes one by its <collate clause>, a domain likewise, and an expression, ORDER BY or GROUP BY names one for itself. These are built in.

Collation Order Pad
SQL_TEXT, UCS_BASIC Unicode scalar value, so Z before a PAD SPACE
UNICODE Language-neutral, by base letter, then accent, then case NO PAD
UNICODE_SV, UNICODE_DE_PHONEBOOK, ... UNICODE as a language orders it NO PAD
CASE_INSENSITIVE Scalar value of each character's case folding PAD SPACE

A language's collation is UNICODE with that language's letters where its dictionaries put them. UNICODE_SV sorts å, ä and ö after z, UNICODE_ES puts ñ after n, UNICODE_EL puts Greek before Latin, and UNICODE_ZH sorts Chinese by pinyin. The order comes from CLDR 38, the Unicode Consortium's locale data, which has one or more types of collation for each locale.

A locale's usual type is named UNICODE_ and the CLDR locale, such as UNICODE_SV or UNICODE_ZH_HANT. Each of these is built in.

AF AM AR AS AZ BE BG BN BO BR BS BS_CYRL CEB CHR CS CY DA DSB DZ EE EL
EN_US_POSIX EO ES ET FA FA_AF FF_ADLM FI FIL FO FR_CA GL GU HA HAW HE HI HR
HSB HU HY IG IS JA KA KK KL KM KN KO KOK KU KY LKT LN LO LT LV MK ML MN MR MT
MY NB NE NN OM OR PA PL PS RO RU SE SI SK SL SMN SQ SR SR_LATN SV TA TE TH TK
TO TR UG UK UR UZ VI WAE WO YI YO ZH ZH_HANT

Another type adds its name after the locale's.

Collations Order
UNICODE_DE_PHONEBOOK, UNICODE_DE_AT_PHONEBOOK German phone book, ü as ue
UNICODE_ES_TRADITIONAL, UNICODE_FI_TRADITIONAL, UNICODE_VI_TRADITIONAL, UNICODE_BN_TRADITIONAL, UNICODE_KN_TRADITIONAL the language's traditional order, such as Spanish ch and ll as letters
UNICODE_SV_STANDARD Swedish before 2006, w as v
UNICODE_ZH_STROKE, UNICODE_ZH_ZHUYIN, UNICODE_ZH_BIG5HAN, UNICODE_ZH_GB2312HAN Chinese by strokes, by zhuyin, or as the Big5 and GB 2312 character sets order it
UNICODE_ZH_UNIHAN, UNICODE_JA_UNIHAN, UNICODE_KO_UNIHAN ideographs by radical, then strokes
UNICODE_CS_DIGITS_AFTER Czech with digits after letters
UNICODE_AR_COMPAT, UNICODE_LN_PHONETIC, UNICODE_SI_DICTIONARY, UNICODE_DE_EOR Arabic of compatibility forms, Lingala by sound, Sinhala as its dictionaries order it, German by the European ordering rules
UNICODE_EOR, UNICODE_EMOJI the European ordering rules for Latin, Greek and Cyrillic; emoji in the order of their charts

UNICODE_JA compares three levels, so hiragana and katakana of one sound are equal, and so are strings that differ only in punctuation.

CREATE COLLATION gives one of them another name and pad characteristic. Under PAD SPACE a shorter string compares as if extended with spaces. Under NO PAD it compares as a prefix, so 'a' is less than 'a '.

A literal, parameter or CAST takes the collation of the column it is compared with. Two columns of different collations need COLLATE. A foreign key's columns have the collation of those they reference.

UNIQUE and indexes on a column hold it by its collation, so Bob and bob are duplicates under CASE_INSENSITIVE. An index of a collation serves comparisons by that collation. Its keys are longer than the text, about 16 bytes for each character under UNICODE, which limits an indexed column's length (Appendix D).

RULES

  1. No collation of its name exists in its schema.
  2. The collation it comes from exists. A name of no schema is a built-in.
  3. It is for SQL_TEXT, the one character set.

PRIVILEGES

Its schema's owner, holding USAGE on the collation it comes from. Built-ins are everyone's. COLLATE needs USAGE, which GRANT gives.

ERRORS

SQLSTATE SQLCODE When
2A000 -103 One of its name exists, or no collation of the name it comes from
2A000 -104 Its user does not own the schema, or lacks USAGE
0A000 -900 It is for another character set, or comes from SQL-92's EXTERNAL, DESC, DEFAULT or TRANSLATION

EXAMPLES

CREATE TABLE PEOPLE (NAME VARCHAR(20) COLLATE CASE_INSENSITIVE UNIQUE);
INSERT INTO PEOPLE VALUES ('Bob'), ('Ann');
INSERT INTO PEOPLE VALUES ('bob');
SELECT NAME FROM PEOPLE WHERE NAME = 'BOB';
CREATE COLLATION WITH_PAD FOR SQL_TEXT FROM UNICODE PAD SPACE;
CREATE TABLE WORDS (W VARCHAR(20) COLLATE ANN.WITH_PAD);
INSERT INTO WORDS VALUES ('résumé  '), ('Zebra'), ('apple');
SELECT W FROM WORDS ORDER BY W;
SELECT COUNT(*) FROM WORDS WHERE W = 'apple   ';
CREATE COLLATION WITH_PAD FOR SQL_TEXT FROM UNICODE;
CREATE COLLATION OTHER FOR SQL_TEXT FROM NOPE;
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): UNIQUE ANN.PEOPLE_UNIQUE1 (NAME) of ANN.PEOPLE would be violated: a row of these values is there [X3.135-1986 §6.4 GR2]
 NAME
----------------------
 Bob
(1 row)
OK
OK
OK
 W
----------------------
 apple
 résumé
 Zebra
(3 rows)

----------------------
                    1
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): collation ANN.WITH_PAD exists [ISO/IEC 9075-2:2023 §11.43 SR3] (at character 17)
turborel: SQLCODE -103 (SQLSTATE 2A000): no collation INFORMATION_SCHEMA.NOPE [ISO/IEC 9075-2:2023 §10.7 SR1] (at character 41)

STANDARD

ISO/IEC 9075-2:2023 §4.3.6, §10.7, §11.43, Features F690 and F692. See CONFORMANCE.md.

SEE ALSO

DROP COLLATION, SET COLLATION, CREATE TABLE, GRANT