SQL standard to escape column names?
SqlSql Problem Overview
Is there a SQL standard to escape a column name? If not what works for MySQL and SQLite? does it also work for SQL Server?
Sql Solutions
Solution 1 - Sql
"
Quotation Mark The SQL:1999 standard specifies that double quote (") (QUOTATION MARK) is used to delimit identifiers.
<delimited identifier> ::= <double quote> <delimited identifier body> <double quote>
Oracle, PostgreSQL, MySQL, MSSQL and SQlite all support "
as the identifier delimiter.
They don't all use "
as the 'default'. For example, you have to be running MySQL in ANSI mode and SQL Server only supports it when QUOTED_IDENTIFIER
is ON
.
Solution 2 - Sql
According to SQLite,
'foo'
is an SQL string"foo"
is an SQL identifier (column/table/etc)[foo]
is an identifier in MS SQL`foo`
is an identifier in MySQL
For qualified names, the syntax is: "t"."foo"
or [t].[foo]
, etc.
MySQL supports the standard "foo" when the ANSI_QUOTES
option is enabled.
Solution 3 - Sql
For MySQL, use back ticks `.
For instance:
SELECT `column`, `column2` FROM `table`
Solution 4 - Sql
For MS SQL use [ and ]
SELECT [COLUMN], [COLUMN 2] FROM [TABLE]
Solution 5 - Sql
Putting some answers together:
MS SQL (a.k.a. T-SQL), Microsoft Access SQL, DBASE/DBF: SELECT [COLUMN], [COLUMN2] FROM [TABLE]
MySQL: SELECT `COLUMN`, `COLUMN2` FROM `TABLE`
SQLite, Oracle, Postgresql: SELECT "COLUMN", "COLUMN2" FROM "TABLE"
Please add/edit!
Solution 6 - Sql
For DBASE/DBF use [
and ]
SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] FROM [TABLE]