Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

SQL identifier and string quoting across dialects

Which quotes mean what in SQL: single quotes for strings, double quotes for identifiers, backticks and brackets, escaping, case folding and SQLite quirks.

SQL uses quotes for two different jobs. Single quotes wrap a string literal ('Lisbon'). Double quotes wrap an identifier, which is the name of a table, column or alias ("order"). This is the standard, and PostgreSQL, Oracle, SQL Server (with default settings) and SQLite follow it. MySQL adds backticks for identifiers, and SQL Server adds square brackets.

Quoting an identifier lets you use a reserved word or spaces in a name, and makes the name case-sensitive in most systems. The cost is that you must quote it the same way everywhere afterwards.

SQL
SELECT 'It''s fine' AS text_value, 1 AS "order";

Try it

SQL Strings, identifiers and escaped quotes ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Reserved words as column names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
'string literal'              -- standard
'it''s'                       -- a single quote inside a string is doubled
"identifier"                  -- standard delimited identifier
"say ""hi"""                  -- a double quote inside an identifier is doubled
`identifier`                  -- MySQL, MariaDB (and accepted by SQLite)
[identifier]                  -- SQL Server (and accepted by SQLite)
N'unicode string'             -- SQL Server, Oracle, standard national literal
Form Meaning
'text' A string value. Always a value, in every dialect.
"name" An identifier in standard SQL. In MySQL (default mode) a string.
`name` An identifier in MySQL and MariaDB.
[name] An identifier in SQL Server.

Dialect differences

Topic SQLite PostgreSQL MySQL SQL Server Oracle
Identifier quotes "x", `x`, [x] "x" `x` ("x" with ANSI_QUOTES) [x], "x" "x"
Double quotes make a string? only as a fallback no yes (default) no no
Backslash escapes in strings no no (unless E'...') yes (default) no no
Unquoted names fold to keeps case, matches ignoring case lower case as typed (table names depend on the OS) as typed, collation decides upper case
Quoted names case-insensitive case-sensitive case depends on settings collation decides case-sensitive
SQL
-- PostgreSQL: these are three different things
SELECT * FROM Users;      -- the table users
SELECT * FROM "Users";    -- the table Users (capital U)
SELECT * FROM "users";    -- the table users again

-- MySQL: backticks, and a backslash is an escape in strings
SELECT `order` FROM `group` WHERE note = 'it\'s fine';

-- SQL Server
SELECT [order] FROM [group];

-- Oracle: unquoted names are upper-cased, so these are the same table
SELECT * FROM employees;
SELECT * FROM EMPLOYEES;
SELECT * FROM "employees";   -- a different table, if it exists

SQLite has an unusual rule: if a double-quoted name does not match any column, it silently becomes a string literal. That means WHERE name = "Lisbon" can seem to work, and WHERE nmae = "Lisbon" returns no rows instead of an error. Always use single quotes for strings so a typo fails loudly.

Usage notes

  • Strings use single quotes everywhere. Doubling a single quote ('') is the portable escape. Do not build strings with \'; it only works in MySQL.
  • Never insert user input into SQL text by quoting it. Escaping rules differ by dialect, and mistakes cause injection holes. Use bind parameters (SQL injection).
  • Avoid quoted identifiers in your own schema. Choose plain snake_case names that are not reserved words, so you never need quotes. Names such as user, order, group, index and key are the usual culprits.
  • Quoting changes case rules. Once a PostgreSQL or Oracle name is created in quotes with capital letters, every query must quote it exactly.
  • Aliases may be quoted when they contain spaces (AS "total sales"). Use that for display only; reports generally want simple aliases.
  • A column named like a keyword still works when quoted, but tools, ORMs and migrations may mishandle it. Rename it.