109

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?

Dale K
  • 21,987
  • 13
  • 41
  • 69

6 Answers6

99

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.

Nae
  • 12,111
  • 5
  • 46
  • 74
Dean Harding
  • 69,493
  • 11
  • 141
  • 178
84

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.

mb21
  • 31,690
  • 8
  • 105
  • 126
tc.
  • 33,206
  • 5
  • 76
  • 96
  • 3
    Note that SQLite allows `'foo'` to be interpreted as an identifier if the context wouldn't allow a string, and `"foo"` to be interpreted as a string if the context wouldn't allow an identifier, though there is a note that this behaviour may be removed in future versions. – thomasrutter Sep 02 '16 at 05:49
  • So, how do you do "t".* ? – Loenix Nov 03 '16 at 14:42
  • 3
    @thomasrutter Yeah I totally got bitten by this behavior… Tried to use `WHERE "nonexistent_column" = 0` and sqlite just happily executed it pretending that my `"nonexistent_column"` was a string. Fully qualifying the name as `"my_table"."nonexistent_column"` forces sqlite into behaving more strictly. – Rufflewind Jan 18 '17 at 03:08
  • `foo`, `"foo"` and `'foo'` did not work for me with MySQL. It required backticks. And to make matters worse, MySQL was providing [useless error messages](https://dba.stackexchange.com/q/229991/141074). – jww Feb 17 '19 at 19:34
22

For MySQL, use back ticks `.

For instance:

SELECT `column`, `column2` FROM `table`
jww
  • 90,984
  • 81
  • 374
  • 818
Kerry Jones
  • 21,600
  • 11
  • 59
  • 88
21

For MS SQL use [ and ]

SELECT [COLUMN], [COLUMN 2] FROM [TABLE]
BoltBait
  • 11,262
  • 9
  • 58
  • 86
4

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!

orangecaterpillar
  • 579
  • 7
  • 12
2

For DBASE/DBF use [ and ]

SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] FROM [TABLE]
Serhii Matrunchyk
  • 8,473
  • 4
  • 34
  • 45