4.2 SQL Identifiers

SQL syntax requires users to supply names for elements such as tables, views, cursors, and columns when they define them. SQL statements must use those names to refer to the table, view, or other element. In syntax diagrams, SQL identifiers are shown in lowercase type.

The maximum length for SQL identifiers is 32 characters.

There are two types of SQL identifiers:

Conventional identifiers
Delimited identifiers enclosed in double quotation marks

4.2.1 Conventional Identifiers

Unless they are delimited identifiers (see "Delimited Identifiers"), SQL identifiers must:

Begin with an uppercase or lowercase letter
Contain only letters, digits, or the underscore character (_)
Not be reserved words

Except for delimited identifiers, SQL does not distinguish between uppercase and lowercase letters in SQL identifiers. It converts all names to lower case, but statements can refer to the names in mixed case. The following examples show some of the characteristics of conventional identifiers:

-- Names are case-insensitive:
CREATE TABLE TeSt (CoLuMn1 CHAR);
INSERT INTO TEST (COLUMN1) VALUES('1');
1 record inserted.
SELECT * FROM TEST;
COL
---
1
1 record selected
 TABLE TEST;
COLNAME                          NULL ?       TYPE        LENGTH
-------                          ------       ----        ------
column1
-- Cannot use reserved words:
CREATE TABLE TABLE (COL1 CHAR);
CREATE TABLE TABLE (COL1 CHAR);
             *
error(-20003): Syntax error

4.2.2 Delimited Identifiers

Delimited identifiers are SQL identifiers enclosed in double quotation marks ("). Enclosing a name in double quotation marks preserves the case of the name and allows it to be a reserved word and special characters. (Special characters are any characters other than letters, digits, or the underscore character.) Subsequent references to a delimited identifier must also use enclosing double quotation marks. To include a double-quotation-mark character in a delimited identifier, precede it with another double-quotation mark.

The following SQL example shows some ways to create and refer to delimited identifiers:

CREATE TABLE "delimited ids"
      ( """"         CHAR(10),
        "_uscore"    CHAR(10),
        """quote"    CHAR(10),
        " space"     CHAR(10) );
INSERT INTO "delimited ids" ("""") VALUES('text string');
1 record inserted.
SELECT * FROM "delimited ids";
"            _USCORE      "QUOTE        SPACE
-            -------      ------       ------
text strin
1 record selected
CREATE TABLE "TABLE" ("CHAR" CHAR);