Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Delimited Identifiers

Delimited identifiers are identifiers which do not need to follow the rules of regular identifiers. Such identifiers can include sequence of printable characters excluding those which are not allowed to use in delimited identifiers in the specified database. Usually delimited identifiers are used when the user need to use SQL reserved word as an identifier.

The rules for using delimited identifiers in various databases are shown in the following table.

TABLE 16. Delimited Identifiers
Database
Rules for Using Delimited Identifiers
Oracle
In Oracle, delimited identifier is a sequence of printable characters enclosed in double quotes. It cannot contain double quotation marks and exceed 30 characters in length not counting the double quotes. It is allowed to use SQL reserved words as delimited identifiers.
For example:
SELECT book_id, "TYPE", author INTO...
In contrast to regular identifiers, delimited identifiers in Oracle are case sensitive. That is why you cannot name the column like "TYPE" (as it is shown in the example) and then refer to this column like "Type" or "type".
IBM DB2
Unix, Windows
In IBM DB2, delimited identifier is a sequence of characters enclosed in double quotes. If you need to represent double quotation mark within the delimited identifier you should use two consecutive double quotation marks without a space between them.
For example, if there is a column in the table, which has a name "Last "Name"" , then to refer to this column you must type something like:
"Last ""Name"""
It is allowed to use SQL reserved words as delimited identifiers. In contrast to regular identifiers, delimited identifiers in IBM DB2 are case sensitive.
OS/390
In IBM DB2 for OS/390, delimited identifier is a sequence of characters enclosed in escape characters. The escape character is a double quotation mark ("), except for those cases when the string delimiter is set to double quotation mark. In this case the escape character is apostrophe ('). It is allowed to use SQL reserved words as delimited identifiers.
If you need to represent escape character within the delimited identifier you should use two consecutive escape characters without a space between them.
The necessary shift characters must be present if there are double byte characters in a delimited identifier.
AS/400
In IBM DB2 for AS/400, delimited identifier is a sequence of characters enclosed in escape characters. The length of a delimited identifier includes the two escape characters only for column names. The escape character is a double quotation mark ("), except for those cases when the string delimiter is set to double quotation mark. In this case the escape character is apostrophe ('). To use SQL reserved words as delimited identifiers, you should specify them in uppercase.
You cannot use the following characters within delimited identifiers:
X'00' through X'3F' and X'FF'
Microsoft SQL Server
In Microsoft SQL Server delimited identifier is a sequence of characters enclosed in double quotation marks (") as default. When quoted_identifier option is set off, only brackets ([]) are used to delimit identifiers and double quotation marks can be used to delimit character strings. It is allowed to use SQL reserved words as delimited identifiers.
The length of the delimited identifier cannot exceed 128 characters not counting the delimiter characters. The body of the identifier can contain any combination of characters in the current code page except for the delimiting characters themselves.
Sybase
ASE
In Sybase Adaptive Server Enterprise, delimited identifier is a 28 characters long identifier enclosed in double quotes, which denotes table, view or column. You cannot use it to identify other objects in the database. Delimited identifier can be a reserved word, can begin with a number or with another non-alphabetical character and can include those characters, which are not allowed to use in regular identifiers. Before creating or referencing a delimited identifier you must execute:
set quoted_identifier on
Characters or data strings cannot be enclosed in double quotes, while the quoted_identifier option is turned on .
ASA
The same as for the ASE. The only difference is that in Adaptive Server Anywhere the quoted_identifier option is set to on as a default and the maximum length for the delimited identifier is 126 characters.
MySQL
prior to 3.23.6
Not supported
3.23.6 or later
In MySQL delimited identifier is an identifier enclosed in back ticks (`) by default. When MySQL is running in ANSI double quotes (" ) will also work to quote identifiers. Delimited identifier cannot contain ASCII(0), ASCII(255) or the quoting character. If the delimited identifier is a reserved word or contains special characters, you must always quote it with a ` (back tick).
For example:
SELECT * FROM `types` WHERE `types`.id < 10;
Pervasive.SQL
In Pervasive.SQL, delimited identifier is an identifier enclosed in double quotes, which can contain any combination of characters and can contain reserved words. If you need to represent double quotes within the delimited identifier, you should use a pair of double quotes without a space between them.
The length is the same as for the regular identifier, counting double quotes.
For example:
Column_name123456789 - (regular identifier, 20 characters)
"Column_name1234567" - (delimited identifier, 20 characters)


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.