How to Insert a Single Quote in SQL

Problem:

You want to insert a record that contains a single quote symbol, e.g., "O’Hara", into an SQL table.

Example:

Our database has a table named employees with data in the following columns: id (primary key), first_name, last_name, and department.

Let’s say we want to insert a new employee in the sales department: William O’Hara. It results in a syntax error if you write it as is. The apostrophe is treated as if it is a closing quote for a string value.

Solution:

To avoid the syntax error, replace the apostrophe with two single quote symbols:

INSERT INTO employees(id, first_name, last_name, department)
VALUES (5, 'William', 'O''Hara', 'Sales');
SELECT * FROM employees;

The result of the query is:

Discussion:

If you want to write a single quote (') in a SQL string, you have to write to consecutive quotes (''). The name of Wiliam O'Hare is written like this in SQL: 'O''Hare'. The first quote is the opening apostrophe of the string, the second and third quotes represent the apostrophe in the name, the fourth quote is the closing apostrophe of the string.

This solution is universal for every SQL dialect. Apostrophes entered by this method are also stackable. That is, use four single quotes if you have two apostrophes, and so on. It is also worth noting that this method may be used in any query and anywhere in a query. For example, it is allowed in a WHERE or HAVING clause in a SELECT query.