How to Escape Single Quotes in SQL

Problem:

You need to escape a single quote in SQL – i.e. include a name with an apostrophe (e.g. Lay’s) in a string.

Example:

You are trying to insert some text data into your table in SQL, like so:

INSERT INTO customer (id, customer_name)
VALUES (501, 'Chipita');

As you are trying to insert a text string (the customer name) you must indicate the start and end of the name with single quotes.

However your next customer, Lay’s, has a name that already contains a single quote (‘) and you wish to keep it as part of your string.

Solution:

Escape the single quote. Here’s what you’d write:

INSERT INTO customer (id, customer_name)
VALUES (502, 'Lay''s');

Here’s the customer table after you run the command:

Discussion

In the example above, you want to insert a name with an apostrophe (Lay's) into an SQL database. To do it, you simply need to use two quotes in the name instead of one ('Lay''s').

This method works with Oracle, SQL Server, MySQL, and PostgreSQL database systems. They also support other ways of getting around the issue:

• MySQL and PostgreSQL place a backslash before the quote, like so:

  INSERT INTO customer (id, customer_name)
  VALUES (502, 'Lay\'s');
  

• Oracle uses “literal quoting” – putting the letter “q” in front, followed by single quotes enclosing square brackets:

  INSERT INTO customer (id, customer_name)
  VALUES (502, q'[Lay's]');
  

You use the same method to escape single quotes in SELECT statements. For example, to select Lay’s, you could run the following query:

SELECT id, customer_name
FROM customer
WHERE customer_name = 'Lay''s';