How to escape a single quote in SQL

1Double it: 'It''s'Recommended

Inside a single-quoted string, write every apostrophe as two: 'O''Brien'. The database stores one quote, so the value is seven characters long, not eight. A string with several apostrophes doubles each one. The rule is the same wherever a string literal appears: the VALUES list of an INSERT (here a multi-row insert), a WHERE comparison, or the new value in an UPDATE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);

-- Inside a single-quoted string, write each ' as ''
INSERT INTO customers (name) VALUES
    ('O''Brien'),
    ('Rock ''n'' Roll Diner'),
    ('Smith');

-- The doubled quote is stored as one character
SELECT id, name, length(name) AS len FROM customers;

-- The same rule applies in WHERE, UPDATE and everywhere else
SELECT id FROM customers WHERE name = 'O''Brien';

UPDATE customers SET name = 'Smith''s Bakery' WHERE id = 3;
SELECT name FROM customers WHERE id = 3;

The table shows O'Brien with a length of 7 and Rock 'n' Roll Diner with 19: each pair became a single character. The WHERE filter finds id 1, and the update stores Smith's Bakery. Doubling works in SQLite, PostgreSQL, MySQL, SQL Server and Oracle alike, so it is the form to use in scripts that must run on more than one of them.

2Search for a quote with LIKE '%''%'

A LIKE pattern is an ordinary string literal, so the apostrophe in it is doubled too: '%''%' means "anything, a quote, anything". The quote isn't a wildcard, so it needs no ESCAPE clause. To get the position of the quote instead, pass instr() the four-character literal '''': an opening quote, a doubled quote, a closing quote, which is a string holding just '.

CREATE TABLE products (name TEXT);
INSERT INTO products VALUES
    ('Kids'' Shoes'), ('Men''s Socks'), ('Rain Jacket'), ('Women''s Hat');

-- Every name that contains an apostrophe
SELECT name FROM products WHERE name LIKE '%''%';

-- Names that end in 's followed by one more word
SELECT name FROM products WHERE name LIKE '%''s %';

-- instr() finds the position of the quote; '''' is a string holding just '
SELECT name, instr(name, '''') AS pos
FROM products
WHERE instr(name, '''') > 0;

The first query returns the three names with an apostrophe and skips Rain Jacket. The '%''s %' pattern drops Kids' Shoes, whose quote isn't followed by s, and keeps Men's Socks and Women's Hat. instr() reports the quote at positions 5, 4 and 6, counting from 1. SQL Server and MySQL spell it CHARINDEX and INSTR, PostgreSQL strpos; the LIKE pattern itself is the same everywhere.

3Build a quote with char(39), escape text with replace()

char(39) returns the character with code 39, which is the single quote, so you can concatenate one into a string without typing it. That helps when the SQL is itself inside a quoted string somewhere and the doubled quotes start to pile up. Going the other way, replace(text, '''', '''''') doubles every quote in a value, which is how you turn stored text into a literal for a script you generate. SQLite's quote() does that and adds the outer quotes.

CREATE TABLE notes (body TEXT);

-- char(39) is the single quote, so no escaping is needed at all
INSERT INTO notes VALUES ('It' || char(39) || 's done');
SELECT body, char(39) = '''' AS same FROM notes;

-- replace() doubles every quote, for text you paste into SQL by hand
SELECT replace(body, '''', '''''') AS escaped FROM notes;

-- SQLite's quote() adds the outer quotes too
SELECT 'INSERT INTO notes VALUES (' || quote(body) || ');' AS stmt
FROM notes;

The inserted row reads It's done, and same is 1: char(39) and '''' are the same one-character string. replace() gives It''s done, and quote() builds the whole statement INSERT INTO notes VALUES ('It''s done');, ready to run. SQL Server and MySQL also call the function CHAR(39); PostgreSQL calls it chr(39). Generating SQL text like this is fine for scripts and exports, but don't use it to put user input into a query; that is what parameters are for.

4Double quotes are for identifiers, not strings

In standard SQL, double quotes wrap a name: a table or column that is a reserved word, contains a space or needs its exact case. "order" is the column named order, not the text order. So "It's" doesn't sidestep the apostrophe problem; it asks for a column called It's. The second query below shows the quiet way this goes wrong: "note" matches a column name, so the filter compares the column with itself.

-- Double quotes name tables and columns, e.g. reserved words or spaces
CREATE TABLE "to do" ("order" INTEGER, note TEXT);
INSERT INTO "to do" VALUES (1, 'gift wrap'), (2, 'no rush');
SELECT "order", note FROM "to do";

-- "note" is the column, so this compares note with itself: every row matches
SELECT "order" FROM "to do" WHERE note = "note";

-- A double-quoted value is not a string: this fails
SELECT * FROM "to do" WHERE note = "no rush";

The quoted names work, and WHERE note = "note" returns both rows, although no row holds the text note. The last query stops with no such column: "no rush" - should this be a string literal in single-quotes?. SQLite has a legacy quirk here: when a double-quoted name matches no column, the library falls back to treating it as a string literal, so in an app that uses SQLite with its default build settings, or an older sqlite3 shell, that query would quietly return the no rush row. The SQLite on this page has the fallback turned off, as the sqlite3 command-line shell has since version 3.41, so you see the error. Don't rely on the fallback: it turns into the self-comparison above as soon as someone adds a column with that name. PostgreSQL and SQL Server reject double-quoted strings; MySQL accepts them unless ANSI_QUOTES is on (see section 6).

5A backslash is not an escape in standard SQL

Most programming languages escape a quote with a backslash, and MySQL does too by default, which is why \' is a common guess. Standard SQL has no backslash escapes: inside '...' a backslash is an ordinary character and stays as typed. That makes Windows paths and regular expressions easy to write, but it means \n is two characters, not a newline, and 'It\'s' is a syntax error.

-- A backslash is an ordinary character in standard SQL
SELECT 'a\b' AS s, length('a\b') AS len;
SELECT 'C:\temp\new' AS path;

-- \n is two characters too; char(10) is a real newline
SELECT length('a\n') AS typed, length('a' || char(10)) AS newline;

-- So \' escapes nothing: the string ends at the quote after the backslash
SELECT 'It\'s' AS s;

'a\b' prints a\b with a length of 3, and the path keeps both backslashes. 'a\n' is three characters, while 'a' || char(10) is two: the letter and a real newline. The last line fails with unrecognized token: "' AS s;": the string 'It\' ended at the second quote, and the leftover s' AS s; opens a string that never closes. PostgreSQL behaves the same way for plain strings and only reads backslash escapes inside E'...'; SQL Server never does. In MySQL, 'a\b' means something else, so write 'a\\b' there for a literal backslash.

6Other databases

Doubling the quote works everywhere, so it is the safe choice. Each database also has its own alternatives, which you will see in existing code; every form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server (Babelfish). Oracle doubles quotes the same way and also has an alternative quoting syntax, q'[It's]'.