Identifiers

Normal identifiers

Most dialects support [a-zA-Z_] as first character and [a-zA-Z0-9_] as rest of the characters. The differences from this are listed below:

• BigQuery: single dashes (-) can be used, but not at the beginning or end.
• DB2: first char can only be a (uppercase) letter (a lowercase letter gets converted to uppercase).
• Hive: (no differences)
• MariaDB: no first-letter restrictions. The characters [a-zA-Z0-9_$] and unicode letters are allowed everywhere. Can begin with digit, but can't only contain digits.
• MySQL: same as MariaDB.
• N1QL: (no differences)
• PL/SQL: can't start with _. Allows $, # in rest of the identifier.
• PostgreSQL: additionally $ after first char. Also unicode letters are allowed.
• Redshift: also unicode letters are allowed.
• Spark: Seems like the usual syntax is allowed. But the docs are confusing.
• SQLite: (no differences)
• Transact-SQL: @ and # are allowed as first chars plus $ in the rest. Also unicode letters are allowed. Though the beginning @ signifies a local variable or parameter and # a temporary table or procedure.
• Trino: [a-zA-Z0-9_]+, no first-letter restrictions

Delimited identifiers

SQL standard specifies double-quotes ".." for delimited identifiers. There is a considerable variation in implementations:

• `..` BigQuery
• ".." DB2
• `..` Hive
• `..`, (".."¹, [..]²) MariaDB
• `..`, (".."¹) MySQL
• `..` N1QL
• ".." PL/SQL
• "..", U&".." PostgreSQL
• ".." Redshift
• `..` Spark
• "..", `..`, [..] SQLite
• ".."³, [..] Transact-SQL
• "..", `..` Trino

Notes:

1. when ANSI_QUOTES mode enabled
2. when MSSQL mode enabled
3. unless QUOTED_IDENTIFIER option has been set OFF