Delimited Identifiers

Delimited identifiers are object names enclosed in double quotes. Using delimited identifiers allows you to avoid certain restrictions on object names.

You can use double quotes to delimit table, view, and column names; you cannot use them for other database objects.

Delimited identifiers can be reserved words, can begin with nonalphabetic characters, and can include characters that would not otherwise be allowed. They cannot exceed 253 bytes. A pound sign (#) is illegal as a first character of any quoted identifier.

Before you create or reference a delimited identifier, execute:

set quoted_identifier on

This allows SAP ASE to recognize delimited identifiers. Each time you use the quoted identifier in a statement, you must enclose it in double quotes. For example:

create table "1one"(col1 char(3))
select * from "1one"
create table "include spaces" (col1 int)

Note: You cannot use delimited identifiers with bcp, as these identifiers may not be supported by all front-end products, and may produce unexpected results when used with system procedures.

While the quoted_identifier option is turned on, use single quotes around character or date strings. Delimiting these strings with double quotes causes SAP ASE to treat them as identifiers. For example, to insert a character string into col1 of 1onetable, use:

insert "1one"(col1) values ('abc')

rather than:

insert "1one"(col1) values ("abc")

To insert a single quote into a column, use two consecutive single quotation marks. For example, to insert the characters “a’b” into col1, use:

insert "1one"(col1) values('a''b')

Syntax that Includes Quotes

When you set the quoted_identifier option to on for a session, use double quotes to delimit object names that may cause syntax errors. Use single quotes for character strings. When you set the quoted_identifier option to off for a session (the default), use double or single quotes to delimit character strings (you cannot quote identifiers).

This example creates table 1one, which, because its name starts with a digit, fails the rules for identifiers and must be set in quotes:

set quoted identifier on
go
create table "1one" (c1 int)

Although create table and most other SQL statements require an identifier to name a table or other SQL object, some commands, functions, and so on require that you supply an object name as a string, whether or not you set the quoted_identifier option to on. For example

select object_id('1one')

-----------------------
                896003192

You can include an embedded double quote in a quoted identifier by doubling the quote. This creates a table named embedded”quote:

create table "embedded""quote" (c1 int)

However, you need not double the quote when the statement syntax requires the object name to be expressed as a string:

select object_id('embedded"quote')

Bracketed Delimited Identifiers

Bracketed identifiers are supported. The behavior is identical to that of quoted identifiers, with the exception that you need not set the quoted_identifier option to on to use them.

create table [bracketed identifier](c1 int)

Support for brackets with delimited identifiers increases platform compatibility.