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.