SQL Dialects Reference/Data structure definition/Delimited identifiers

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Delimited identifiers[edit | edit source]

There are regular identifiers and delimited identifiers to denominate database objects like tables or columns. You must apply delimited identifiers if you want to use SQL keywords like 'FROM' as identifiers or special characters within its name. Delimited identifiers are labeled by the following techniques.

Dialect Sample Comment
Standard "identifier" The SQL standard calls these "delimited identifiers". The SQL standard requires that delimited identifiers are case-sensitive, although not all servers enforce this.
DB2 "identifier" Case sensitive per standard and also allows special characters.
Firebird "identifier" Case sensitive per standard.
Ingres "identifier" Case sensitivity for delimited identifiers is specified when a database is created. For compliance with ANSI/ISO Entry SQL-92, delimited identifiers must be case sensitive.
Linter "identifier" Case sensitive per standard.
MonetDB "identifier" Case sensitive per standard.
MSSQL [identifier]
"identifier"[1]
Case sensitivity is set by a per-database option.
MySQL `identifier`
"identifier"[2]
Oracle "identifier" Case sensitive per standard. Case insensitive when not double-quoted: They are converted to uppercase before the execution of any statement, e.g: 'CrEAtE table MyTable' is converted to 'CREATE TABLE MYTABLE'. This conforms to the standard.
PostgreSQL "identifier" Case sensitive per standard. Case insensitive when not double-quoted (converted to lower case - while it's upper case per standard).
SQLite [identifier]
"identifier"
'identifier'
identifier
Never case sensitive, even when quoted. Bracketed keywords are always understood as identifiers. Double-quoted keywords are understood as identifiers if previously seen as such, but are otherwise interpreted as string literals. Single-quoted keywords are interpreted as string literals where such are allowed, but otherwise as identifiers. Some keywords may be used as identifiers even when not quoted.
Virtuoso "identifier"  

Notes:

  1. If the quoted_identifier option is set on — otherwise, "identifier" would be parsed as a string in single quotes.
  2. If running in ANSI mode.