How to insert a value that contains an apostrophe (single quote)?

Question

What is the correct SQL syntax to insert a value with an apostrophe in it?

Insert into Person

(First, Last)

Values

'Joe',

'O'Brien'

I keep getting an error as I think the apostrophe after the O is the ending tag for the value.

Answer 1

To escape the apostrophe in SQL, you can use this code:

INSERT INTO Person

    (First, Last)

VALUES

    ('Joe', 'O''Brien')

              /\

          right here  

Similarly, you can also do for SELECT Clauses:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

The single quote or apostrophe is the special character in SQL which specifies the beginning and end of string data i.e. to use it as part of your literal string data you need to escape the special character.

Note: When you manually edit data via the raw SQL interface then only these issues arise. But, in code there are frameworks and techniques which will take care of the escaping special characters, SQL injection and so on.

Answer 2

When you are utilizing an SQL statement that you are attempting to insert a value which contains an apostrophe, also known as a single quote, you must backslash it so that you duplicate it. This allows the database to know the end of the string is not the apostrophe itself but a part of the string.

Here is the correct way you should write your SQL insert statement:

INSERT INTO Person (First, Last)

VALUES ('Joe', 'O''Brien');

In the example above, the apostrophe in O'Brien is escaped as O''Brien. That informs SQL that it's part of the string, rather than the end of the value.