Predefined Character Sets

In SQL, a Character set may be a Character set defined by a national or international standard, by your DBMS or by a user of SQL-data.

Standard-defined Character sets consist of a set of characters predefined by some standards body and have a default Collation that is the order of the characters in the relevant standard. The default Collation has the PAD SPACE characteristic. The SQL Standard requires a DBMS to support, at a minimum, these standard-defined Character sets: SQL_CHARACTER, GRAPHIC_IRV (also called ASCII_GRAPHIC), LATIN1, ISO8BIT (also called ASCII_FULL) and UNICODE (also called ISO10646).

Implementation-defined Character sets consist of a set of characters predefined by your DBMS and have a default Collation that is also defined by your DBMS. The default Collation may have either the PAD SPACE characteristic or the NO PAD characteristic. The SQL Standard requires a DBMS to support, at a minimum, this implementation-defined Character set: SQL_TEXT.

SQL_CHARACTER

The SQL_CHARACTER Character set is an 8-bit Character set that consists of the 83 <SQL language character>s. These are found in codepage 1252.

SQL_CHARACTER characters, and their codepage 1252 Form-of-use codes, are:

[NON-PORTABLE] The default Collation for the SQL_CHARACTER Character set is non-standard because the SQL Standard requires implementors to define SQL_CHARACTER's default Collation. Most DBMSs will sort the characters of SQL_CHARACTER in the decimal order shown above. [OCELOT Implementation] The OCELOT DBMS that comes with this book has a default Collation (called SQL_CHARACTER) for the SQL_CHARACTER Character set that sorts the characters in their codepage 1252 decimal order.

GRAPHIC_IRV and ASCII_GRAPHIC

The GRAPHIC_IRV Character set (ASCII_GRAPHIC is a synonym) is an 8-bit Character set that consists of 95 characters: all of the SQL_CHARACTER characters, plus an additional 12 characters from codepage 1252. The default Collation for the GRAPHIC_IRV Character set sorts the characters in their codepage 1252 decimal order.

GRAPHIC_IRV characters, and their codepage 1252 Form-of-use codes (in default Collation order)

[NON-PORTABLE] The default Collation for the GRAPHIC_IRV Character set has a non-standard name because the SQL Standard requires implementors to define what a legal <Collation name> is. [OCELOT Implementation] The OCELOT DBMS that comes with this book has a default Collation called ASCII_GRAPHIC for the GRAPHIC_IRV Character set.

LATIN1

The LATINI Character set is an 8-bit Character set that consists of all the characters commonly used in Danish, Dutch, English, Faeroese, Finnish, French, German, Icelandic, Irish, Italian, Norwegian, Portuguese, Spanish and Swedish -- a total of 191 characters: all of the GRAPHIC_IRV characters, plus an additional 96 characters from codepage 1252. The default Collation for the LATIN1 Character set sorts the characters in their codepage 1252 decimal order.

Additional LATIN1 characters, and their codepage 1252 Form-of-use codes (in default Collation order):

[NON-PORTABLE] The default Collation for the LATIN1 Character set has a non- standard name because the SQL Standard requires implementors to define what a legal <Collation name> is. [OCELOT Implementation] The OCELOT DBMS that comes with this book has a default Collation called LATIN1 for the LATIN1 Character set.

ISO8BIT and ASCII_FULL

The ISO8BIT Character set (ASCII_FULL is a synonym) is an 8-bit Character set that consists of 256 characters: all of the LATIN1 characters, plus the rest of the characters (including control characters and graphic characters) from codepage 1252. The default Collation for the ISO8BIT Character set sorts the characters in their codepage 1252 decimal order.

ISO8BIT characters and their codepage 1252 Form-of-use codes (in default Collation order):

[NON-PORTABLE] The default Collation for the ISO8BIT Character set has a non- standard name because the SQL Standard requires implementors to define what a legal <Collation name> is. [OCELOT Implementation] The OCELOT DBMS that comes with this book has a default Collation called ISO8BIT for the ISO8BIT Character set.

UNICODE and ISO10646

The UNICODE Character set (ISO10646 is a synonym) is a 16-bit Character set that consists of every character represented by the Unicode specification (specifically, by The Unicode Standard Version 2.1). Since there are 38,887 characters in this set, we won't include a list in this book. It suffices to say that the UNICODE Character set includes all of the ISO8BIT characters, plus many others. The default Collation for the UNICODE Character set sorts the characters in their Unicode Form-of-use code order.

[NON-PORTABLE] The default Collation for the UNICODE Character set has a non- standard name because the SQL Standard requires implementors to define what a legal <Collation name> is. [OCELOT Implementation] The OCELOT DBMS that comes with this book has a default Collation called SQL_TEXT for the UNICODE Character set.

SQL_TEXT

The SQL Standard requires every DBMS to provide an Ur-Character set named SQL_TEXT. The complete set of SQL_TEXT characters is implementation defined, but SQL_TEXT must contain every <SQL language character> plus every other character that the DBMS supports -- that is, SQL_TEXT (at a minimum) includes all of the UNICODE characters and is therefore at least a 16-bit Character set.

[NON-PORTABLE] The default Collation for the SQL_TEXT Character set must be called SQL_TEXT too, but is otherwise non-standard because the SQL Standard requires implementors to define the default Collation and Form-of-use of SQL_TEXT. [OCELOT Implementation] The OCELOT DBMS that comes with this book sorts the characters of SQL_TEXT in their Unicode Form-of-use code order.

There are some curious consequences to making SQL_TEXT (and UNICODE) a 16-bit, rather than an 8-bit Character set.

The consequences are the result of the fact that all <identifier>s and other character strings in INFORMATION_SCHEMA, all <SQL-server name>s, all <Connection name>s, all <AuthorizationID>s, and the results of many functions (e.g.: USER, CURRENT_USER, SESSION_USER, SYSTEM_USER) are SQL_TEXT character strings.

SQL_TEXT, however, is normally not the default Character set (the default Character set is almost always an 8-bit Character set) -- and SQL specifically prohibits comparisons between strings that belong to different Character sets. (The rules require that, for such comparisons, one string be translated to the other string's Character set first -- see the TRANSLATE function" in Chapter 7, "Character Strings".)

So, will you have special trouble when you want to display INFORMATION_SCHEMA strings, <AuthorizationID>s, <SQL-server name>s, <Connection name>s or the like? There are two possibilities, and the answer is usually "NO" to both.

Possibility 1

Consider the following SQL statements:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;
SELECT SESSION_USER FROM INFORMATION_SCHEMA.TABLES;

The SQL Standard allows a DBMS to decide whether there should be an automatic translation from SQL_TEXT to the default Character set when an SQL statement assigns an SQL_TEXT string to a host language variable or parameter. In such cases, most DBMSs do the automatic translation for you (as, for example, the OCELOT DBMS that comes with this book does) -- so, to display the result of a SELECT from INFORMATION_SCHEMA or the result from a function that returns an SQL_TEXT string, you won't need to do anything special. It won't normally be necessary to use the TRANSLATE function to translate the retrieved values from 16-bit SQL_TEXT characters into 8-bit, readable format (or vice versa) -- just write your SQL statement as if the Character set difference doesn't exist. (In the example above, by the way, the ASCII <identifier> TABLE_NAME would also be automatically converted to a SQL_TEXT string by your DBMS before the search was done.)

Possibility 2

Consider this SQL statement:

   SELECT TABLE_NAME
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  TABLE_NAME = 'ocelot';

The SQL Standard does not allow a DBMS to decide whether there should be an automatic translation from SQL_TEXT to the default Character set when an SQL statement involves a comparison between a SQL_TEXT string and a default Character set string -- but the rules do allow a DBMS to provide a predefined Translation for this purpose. In such cases, most DBMSs will use this option to do an automatic translation for you (as, for example, the OCELOT DBMS that comes with this book does) -- so, to compare SQL_TEXT strings with default Character set strings, you won't need to do anything special. It won't normally be necessary to use the TRANSLATE function to translate the retrieved values from 16-bit SQL_TEXT characters into 8-bit, readable format (or vice versa) -- just write your SQL statement as if the Character set difference doesn't exist.

A second consequence of the fact that SQL_TEXT is 16-bit and the default Character set is usually 8-bit involves the use of <AuthorizationID>s, <SQL-server name>s and <Connection name>s. Although <AuthorizationID>s, <SQL-server name>s and <Connection name>s are stored as 16-bit SQL_TEXT strings, the SQL-Connection arguments in CONNECT TO are inevitably in 8-bit sets, since ASCII is used to type them. If, however, these same arguments are assigned to host language variable parameters, you will see them as 16-bit strings. Therefore, when you have an expression like this:

... WHERE  = 'x' ...

you know that the <character string literal> 'x' is a 16-bit string because <Authorization ID>s are SQL_TEXT strings. But the same <literal> in a CONNECT statement, e.g.:

CONNECT TO 'x';

has to be an 8-bit string because it is typed in ASCII.

One last comment, about parsing SQL_TEXT strings. Consider the following expression, which concatenates a character string with a value from a Column:

... _INFORMATION_SCHEMA.SQL_TEXT 'A ' || column_1

The default Character set for an SQL statement is usually an 8-bit Character set, but the string 'A ' in this example is explicitly introduced as 16-bit SQL_TEXT. Thus, what appears to be two 8-bit characters -- A and <space> -- is, in fact, one 16-bit character, the Unicode value 4120h.

Parsing of SQL statements is always based on 8-bit values regardless of the Character set of a string, so your DBMS will consider the SQL_TEXT string 'A ' to begin after the first single quote mark and end before the second single quote mark; i.e.: although the characters inside the single quotes are 16-bit, the delimiting single quote marks themselves are 8-bit. Thus, when the parser reaches the second single quote mark it knows that the string is over -- despite the fact that in 16-bit, a single quote mark character has not yet been found.