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)
|
