How Do Different SQL Databases Handle Identifier Delimiters?

Database Identifier Delimiters: Delving into Quoting Variations

In the realm of SQL, a common requirement arises when working with identifiers (table names, column names) that may contain non-alphanumeric characters or pose potential conflicts with SQL keywords. To address this, databases employ delimiters known as "delimited identifiers."

Quotations with Backticks and Double-Quotes

By convention, SQL uses double-quotes as the default delimiters for quoted identifiers:

SELECT * FROM "my_table";

However, some databases deviate from this norm. For instance, MySQL and Microsoft SQL Server have introduced variations:

• MySQL: MySQL utilizes back-quotes (`) by default, though it also supports double-quotes:

SELECT * FROM `my_table`;

-- Enabling ANSI mode allows for double-quotes:
SET SQL_MODE=ANSI_QUOTES;
SELECT * FROM "my_table";

• Microsoft SQL Server: Microsoft SQL Server employs brackets ([]) as its default delimiter, while double-quotes are supported with the following setting:

SELECT * FROM [my_table];

-- Turn on quoted identifiers:
SET QUOTED_IDENTIFIER ON;
SELECT * FROM "my_table";

In the case of InterBase and Firebird, they require a modification to the SQL dialect in order to permit delimited identifiers.

Conforming to Standards

While many database systems adhere to the double-quote convention, variations in quoting methods highlight the importance of checking the documentation and adhering to the specific conventions of each DBMS. By doing so, you can avoid syntax errors and ensure that your SQL queries are executed as intended.

The above is the detailed content of How Do Different SQL Databases Handle Identifier Delimiters?. For more information, please follow other related articles on the PHP Chinese website!