Using delimited identifiers

Delimited identifiers are object names enclosed in double quotes. Using delimited identifiers allows you to avoid certain restrictions on object names. Table, view, and column names can be delimited by quotes; other object names cannot.

Delimited identifiers can be reserved words, can begin with non-alphabetic characters, and can include characters that would not otherwise be allowed. They cannot exceed 253 bytes.

Before creating or referencing a delimited identifier, you must execute:

set quoted_identifier on

Each time you use the delimited identifier in a statement, you must enclose it in double quotes. For example:

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

create table "grant"("add" int)
insert "grant"("add") values (3)

While the quoted_identifier option is turned on, do not use double quotes around character or date strings; use single quotes instead. Delimiting these strings with double quotes causes Adaptive Server to treat them as identifiers. For example, to insert a character string into col1 of 1table , use:

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

Do not not use:

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 the quoted_identifier option is set to on, you do not need to use double quotes around an identifier if the syntax of the statement requires that a quoted string contain an identifier. For example:

set quoted_identifier on
create table '1one' (c1 int)

However, object_id() requires a string, so you must include the table name in quotes to select the information:

select object_id('1one')

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

You can include an embedded double quote in a quoted identifier by doubling the quote:

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

However, there is no need to double the quote when the statement syntax requires the object name to be expressed as a string:

select object_id('embedded"quote')

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF