Is there an ANSI requirement on what should be used to quote a string vs a database identifier? It seems, at least for me, this is one of the most irritating parts of working with various databases -- namely, when to use backticks vs. single-quotes vs double-quotes. As an example, here are what it seems to be in Postgres, Oracle, MySQL, SqlServer, and SQLite:

For whatever reason, I prefer the MySQL way of being able to use both single- and double-quotes for strings (which many languages allow, such as python, javascript, etc.), and the backticks seems to work nicely as its so much less commonly used outside of annotating code-objects, and a table or column name often feels like a code object. Is there a standard suggestion on how to do this or is it entirely implementation dependent?

CodePudding user response：

If the following is not the ANSI standard, it's at least the de-facto standard supported by most DBMS-s:

1. single quotes as string encloser for string literals
2. double quotes as string encloser for identifiers
3. if the string encloser exists within the string, double it; don't escape it with a backslash.

Quite some databases actually offer the QUOTE_LITERAL() and QUOTE_IDENT() functions that re-format strings exactly along those rules.

Example:

SELECT
     'The Restaurant called "Harry''s Bar" in Casablanca' -- string expression
 AS  "The Restaurant called ""Harry's Bar"" in Casablanca" -- column name
FROM dual;
-- out  The Restaurant called "Harry's Bar" in Casablanca 
-- out ---------------------------------------------------
-- out  The Restaurant called "Harry's Bar" in Casablanca