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.

Solution 1

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

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

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Solution 2

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

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

Solution 3

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'

Solution 4

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.

Solution 5

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

View more solutions

Comments

[qwerty_so]

AFAIK the Values must be enclosed in braces (which then makes it the same answer as that of @JustinNiessner)

[user462990]

$linkQuestion = str_replace ("'","''", $linkQuestion);(gosh that's hard to read!)

[Jasper de Vries]

Then the problem becomes: how to insert a backtick?

[Jonathan Leffler]

Note that this is not standard SQL any more, though I know Informix, to name but one DBMS, allows it. You should also address how you'd insert a string such as He said, "Don't!" using single quotes and/or double quotes — which can be done: 'He said, "Don''t!"' or "He said, ""Don't!""". When adding a new answer to a long-established question with accepted answers, you must provide new, complete information. Using double quotes is new — but limited to a few DBMS; you should take pains to identify at least one of those that does accept them.

[PhillipOReilly]

Great and much simpler solution and my last name is O`Reilly!

[Learner]

Anyways It is advised to use escape characters . Accidently :) since the day I am using keyboard . I am using accent symbol unintentionally in place of single quote while writing documents. (however in code I use ' as char identifier). Until last year when somebody told me his password and I was unable to login in to his system. we could not figure out until hours that I am typing ' as `.

[nscalf]

While this is not the "right" answer, I'm working with a set of names that don't have this, and I'm doing one off operations, so this immediately solved my problem. Thanks!

[vijesh]

what if the data is Insert into Person (First, Last) Values 'Joe', 'Father's shop' .

[Jamesckel]

This doesn't help if you have to do this through a script, as vijesh mentions. Let's assume the string literal in unalterable. How then do you work with it?

[Suamere]

Is this answer Oracle-Specific? I can't find anything online about this working with SQL, and this PC I'm on doesn't use SSMS. Got a link for Microsoft SQL? Then, how do you insert that's what she said ]' etc ? Does the existence of a square bracket, and possibly followed by a single tick, tell it to break the value?

[Daniel Rust]

Hi @Suamere, I use Oracle so I had to search the web to tell if this is specific or not to it... I found very little about the subject indeed. This acticle suggests it's specific to Oracle. But there might be a different approach for other vendors...