114

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
  • 25,246
  • 15
  • 42
  • 71

6 Answers6

105

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
  • 14,209
  • 7
  • 52
  • 79
Dean Harding
  • 71,468
  • 13
  • 145
  • 180
89

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
  • 34,845
  • 8
  • 116
  • 142
tc.
  • 33,468
  • 5
  • 78
  • 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
24

For MySQL, use back ticks `.

For instance:

SELECT `column`, `column2` FROM `table`
jww
  • 97,681
  • 90
  • 411
  • 885
Kerry Jones
  • 21,806
  • 12
  • 62
  • 89
23

For MS SQL use [ and ]

SELECT [COLUMN], [COLUMN 2] FROM [TABLE]

If the column name contains a ], then escape it by doubling it, so a column named "Column with [brackets]" would be escaped as [Column with [brackets]]].

Scott
  • 4,458
  • 1
  • 19
  • 27
BoltBait
  • 11,361
  • 9
  • 58
  • 87
  • 2
    you can have spaces in column names with ms sql!?! –  May 25 '10 at 01:41
  • 4
    you can! I wouldn't, though. – JMP May 25 '10 at 01:43
  • 1
    Yes, you can have spaces or reserved words as entity names in MSSQL. You just need to [ ] them. – BoltBait May 25 '10 at 01:43
  • 8
    BTW, if you're using [ and ] in MS SQL Server and you want to use the ] character in the name itself (for whatever reason) then you need to escape it with an other ] character (ie use ]] instead of ]). – Daniel James Bryars Jul 16 '11 at 10:00
  • SQL Server can be configured to use quotation marks, see the documentation for `SET QUOTED_IDENTIFIER`. SQL Server (and other DBMSes, like MySQL, Maria, and Oracle DB) allow identifiers to have spaces, if they're quoted. – MikeB Jul 10 '22 at 21:31
6

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
  • 714
  • 9
  • 12
  • For Postgres at least, you can escape double quotes that are embedded in the column name with double-double quotes, e.g. `select 3.14/6 as "A ""slice"" of pi" ` returns `A "slice" of pi` with value `0.52333` – prototype Aug 01 '22 at 20:07
2

For DBASE/DBF use [ and ]

SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] FROM [TABLE]
Serhii Matrunchyk
  • 9,083
  • 6
  • 34
  • 47