Regular and Delimited Identifiers
Identifiers in SQL statements specify names for the following objects:
• Authorization identifier (user, group, or role)
• Column
• Constraint
• Correlation name
• Index
• Location
• Schema
• Synonym
• Table
• View
The name for any of these objects can be specified using regular (unquoted) identifiers or delimited (double-quoted) identifiers. For example:
• Table name specified using a regular identifier in a SELECT SQL statement:
SELECT * FROM employees
• Table name specified using a delimited identifier in a SELECT SQL statement:
SELECT * FROM "my table"
Delimited identifiers let you use special characters in object names. The use of special characters in regular identifiers is restricted. For example, a view name that begins with or consists only of numeric characters must be delimited because a regular identifier cannot begin with the characters 0 through 9, #, @, and $.
Case Sensitivity of Identifiers
Case sensitivity for regular and delimited identifiers is defined by configuration parameters. At the time a database is created, the current setting of those parameters is stored with the database.
By default, regular and delimited identifiers are translated to and stored as lowercase.
The DBMS Server treats database, user, group, role, cursor, and location names without regard to case. Mixed-case database or location names cannot be created.
To determine the settings for the database to which a session is connected
Use the DBMSINFO function, as follows:
SELECT DBMSINFO('DB_NAME_CASE')
and
SELECT DBMSINFO('DB_DELIMITED_CASE')
Restrictions on Identifiers
The following table lists the restrictions for regular and delimited identifiers (the names assigned to database objects):
The following characters cannot be embedded in object names using either regular or delimited identifiers:
DEL (ASCII 127 or X'7F')
To specify double quotes in a delimited identifier, repeat the quotes.
For example:
"""Identifier""Name"""
is interpreted as:
"Identifier"Name"
Trailing spaces are deleted from object names specified using delimited identifiers.
For example:
"space test " (scolumn INT);
creates a table named, space test, with no trailing blanks (leading blanks are retained).
If an object name composed entirely of spaces is specified, the object is assigned a name consisting of a single blank. For example, the following creates a table named “ ”.
" " (scolumn INT);
Comment Delimiters
To indicate comments in interactive SQL, use the following delimiters:
• /* and */
For example:
/* This is a comment */
The /*...*/ delimiters allow a comment to continue over more than one line. For example:
/* Everything from here...
...to here is a comment */
• --
The -- delimiter indicates that the rest of the line is a comment. The comment cannot be continued to another line.
For example:
--This is a comment.