125

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?

Improve this question

6 Answers 6

Reset to default
111

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

97

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.

Improve this answer

4 Comments

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.
So, how do you do "t".* ?
@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.
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.
25

For MySQL, use back ticks `.

For instance:

SELECT `column`, `column2` FROM `table`
Improve this answer

Comments

24

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]]].

Improve this answer

5 Comments

you can have spaces in column names with ms sql!?!
you can! I wouldn't, though.
Yes, you can have spaces or reserved words as entity names in MSSQL. You just need to [ ] them.
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 ]).
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.
7

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!

Improve this answer

1 Comment

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
2

For DBASE/DBF use [ and ]

SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] FROM [TABLE]
Improve this answer

Comments