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

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.

Because a single quote is used for indicating the start and end of a string; you need to escape it.

The short answer is to use two single quotes - '' - in order for an SQL database to store the value as '.

Look at using REPLACE to sanitize incoming values:

• Oracle REPLACE
• SQL Server REPLACE
• MySQL REPLACE
• PostgreSQL REPLACE

You want to check for '''', and replace them if they exist in the string with '''''' in order to escape the lone single quote.

use double quotation marks around the values.

insert into Person (First, Last) Values("Joe","O'Brien")

Use a backtick (on the ~ key) instead;

`O'Brien`

Single quotes are escaped by doubling them up,

The following SQL illustrates this functionality.

declare @person TABLE (
    [First] nvarchar(200),
    [Last] nvarchar(200)
)

insert into @person 
    (First, Last)
values
    ('Joe', 'O''Brien')

select * from @person

Results

First   | Last
===================
Joe     | O'Brien

eduffy had a good idea. He just got it backwards in his code example. Either in JavaScript or in SQLite you can replace the apostrophe with the accent symbol.

He (accidentally I am sure) placed the accent symbol as the delimiter for the string instead of replacing the apostrophe in O'Brian. This is in fact a terrifically simple solution for most cases.

You just have to double up on the single quotes...

insert into Person (First, Last)
values ('Joe', 'O''Brien')

You need to escape the apostrophe. In T-SQL this is with a double apostrophe, so your insert statement becomes:

Insert into Person
(First, Last)
Values
'Joe', 'O''Brien'

The apostrophe character can be inserted by calling the CHAR function with the apostrophe's ASCII table lookup value, 39. The string values can then be concatenated together with a concatenate operator.

Insert into Person
  (First, Last)
Values
  'Joe',
  concat('O',char(39),'Brien')

Another way of escaping the apostrophe is to write a string literal:

insert into Person (First, Last) values (q'[Joe]', q'[O'Brien]')

This is a better approach, because:

1. Imagine you have an Excel list with 1000's of names you want to upload to your database. You may simply create a formula to generate 1000's of INSERT statements with your cell contents instead of looking manually for apostrophes.

2. It works for other escape characters too. For example loading a Regex pattern value, i.e. ^( *)(P|N)?( *)|( *)((<|>)\d\d?)?( *)|( )(((?i)(in|not in)(?-i) ?(('[^']+')(, ?'[^']+'))))?( *)$ into a table.