Delimited Identifiers

By default, the character set of a valid SQL identifier is restricted to letters, digits, underscore, and dollar-sign symbols. If you set the DELIMIDENT environment variable, however, SQL identifiers can also include additional characters from the code set implied by the setting of the DB_LOCALE environment variable.

Delimited Identifier:
 
      .---------------------.
      V                     |
|--"----+-letter----------+-+--"--------------------------------|
        +-digit-----------+
        +-underscore------+
        '-other_character-'
 

If the database supports delimited identifiers, double quotes ( " ) must enclose every SQL identifier in your code, and single ( ' ) quotes, rather than double ( " ) quotes, must delimit all character-string literals.

Delimited identifiers enable you to declare names that are otherwise identical to SQL keywords, such as TABLE, WHERE, DECLARE, and so on. The only type of object for which you cannot specify a delimited identifier is a database name.

Letters in delimited identifiers are case sensitive. If you are using the default locale, letter must be an upper- or lowercase character in the range a to z or A to Z (in the ASCII code set). If you are using a nondefault locale, letter must be an alphabetic character that the locale supports. For more information, see Support for Non-ASCII Characters in Delimited Identifiers (GLS).

Delimited identifiers are compliant with the ANSI/ISO standard for SQL.

When you create a database object, avoid including leading blank spaces or other whitespace characters between the first delimiting quotation mark and the first nonblank character of the delimited identifier. (Otherwise, you might not be able to reference the object in some contexts.)

3If the name of a database server is a delimited identifier 3or if it includes uppercase letters, that database server cannot participate 3in distributed DML operations. To avoid this restriction, use only undelimited 3names that include no uppercase letters when you declare the name or the alias 3of a database server.

Support for Nonalphanumeric Characters

You can use delimited identifiers to specify nonalphanumeric characters in the names of database objects. You cannot use delimited identifiers, however, to specify nonalphanumeric characters in the names of storage objects such as dbspaces, partitions, and blobspaces.

Support for Non-ASCII Characters in Delimited Identifiers (GLS)

When you are using a nondefault locale whose code set supports non-ASCII characters, you can specify non-ASCII characters in most delimited identifiers. The rule is that if you can specify non-ASCII characters in the undelimited form of the identifier, you can also specify non-ASCII characters in the delimited form of the same identifier. For a list of identifiers that support non-ASCII characters and for information on non-ASCII characters in delimited identifiers, see the IBM Informix GLS User's Guide.

Effect of DELIMIDENT Environment Variable

To use delimited identifiers, you must set the DELIMIDENT environment variable. While DELIMIDENTis set , strings enclosed in double quotes ( " ) are treated as identifiers of database objects, and strings enclosed in single quotes ( ' ) are treated as literal strings. If the DELIMIDENT environment variable is not set, however, strings enclosed in double quotes are also treated as literal strings.

If DELIMIDENT is set, the SELECT statement in the following example must be in single quotes in order to be treated as a quoted string:

PREPARE ... FROM 'SELECT * FROM customer';

If a delimited identifier is used in the SELECT statement that defines a view, then the DELIMIDENT environment variable must be set in order for the view to be accessed, even if the view name itself contains no special characters.

Examples of Delimited Identifiers

The next example shows how to create a table with a case-sensitive name:

CREATE TABLE "Proper_Ranger" (...);

The following example creates a table whose name includes a whitespace character. If the table name were not enclosed by double ( " ) quotes, and if DELIMIDENT were not set, you could not use a blank space in the identifier.

CREATE TABLE "My Customers" (...);

The next example creates a table that has a keyword as the table name:

CREATE TABLE "TABLE" (...);

The following example (for Dynamic Server) shows how to delete all the rows from a table that is named FROM when you omit the keyword FROM in the DELETE statement:

DELETE "FROM"; 

Using Double Quotes Within a Delimited Identifier

To include a double quote ( " ) in a delimited identifier, you must precede the double quote ( " ) with another double quote ( " ), as this example shows:

CREATE TABLE "My""Good""Data" (...);