Skip to main content

Delimited Identifiers

Delimited Identifiers

A delimited identifier has the following syntax:

delimited-identifier ::= " delimited-identifier-part { delimited-identifier-part } "
    delimited-identifier-part ::= non-double-quote-character | double-quote-symbol
    double-quote-symbol ::= ""

A delimited identifier is a unique identifier enclosed by delimiter characters. InterSystems SQL supports double quote characters (") as delimiter characters. Delimited identifiers are generally used to avoid the naming restrictions of simple identifiers.

Note that InterSystems SQL uses single quote characters (') to delimit literals. For this reason, delimited identifiers must be specified using double quote characters ("), and literals must be specified using single quote characters ('). For example, '7' is the numeric literal 7, but "7" is a delimited identifier. When an SQL statement is enclosed in double quotes (for example, in Dynamic SQL), double quote characters within that string must be doubled.

An SQL empty string should always be specified as a pair of single quote characters ''. When delimited identifier support is enabled, a pair of double quote characters "" is parsed as an invalid delimited identifier and generates an SQLCODE -1 error.

Delimited Identifier Valid Names

A delimited identifier must be a unique name. Delimited identifiers are not case-sensitive; by convention, identifiers are represented with initial capital letters.

A delimited identifier can be the same as an SQL reserved word. Delimited identifiers are commonly used to avoid concerns about naming conflicts with SQL reserved words.

A delimited identifier may contain almost any printable character, including blank spaces. Most delimited identifier names cannot contain the following characters: comma (,), period (.), caret (^), and the two-character arrow sequence (->); however delimited identifier role names and user names may contain these characters. A delimited identifier classname may contain periods (.). No delimited identifier may begin with an asterisk (*). The following term cannot be used as a delimited identifier: %vid. Violating these naming conventions results in an SQLCODE -1 error.

A delimited identifier used as a table, schema, column, or index name must be able to be converted to a valid class entity name. Therefore, it must contain at least one alphanumeric character. A delimited identifier that begins with a number (or punctuation followed by a number) generates a corresponding class entity name with the letter “n” prefix.

The following example shows a query that makes use of delimited identifiers for both column and table names:

SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'

Note that the delimited identifiers are delimited with double quotes, and the string literal A% is delimited with single quotes.

When specifying a delimited identifier for a table name, you must separately delimit the table name and the schema name. Thus, "schema"."tablename" or schema."tablename" are valid identifiers, but "schema.tablename" is not a valid identifier.

Disabling Delimited Identifier Support

By default, support is enabled for delimited identifiers.

When delimited identifier support is disabled, characters within double quotes are treated as string literals.

You can set delimited identifier support system-wide using the SET OPTION command with the SUPPORT_DELIMITED_IDENTIFIERS keyword.

You can set delimited identifier support system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method DelimitedIdentifiers option. Delimited identifiers are supported by default.

To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

Note:

Delimited identifiers are required by other features of InterSystems IRIS, such as InterSystems IRIS Business Intelligence. As such, take care when disabling them to ensure that other parts of your configuration are not affected.

FeedbackOpens in a new tab