'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.

sqlsql-updatesql-insert


Solution 1:[1]

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

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

Solution 2:[2]

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 3:[3]

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:

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

Solution 4:[4]

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

Solution 5:[5]

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.

Solution 6:[6]

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')

Solution 7:[7]

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.

Solution 8:[8]

use double quotation marks around the values.

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

Solution 9:[9]

This is how my data as API response looks like, which I want to store in the MYSQL database. It contains Quotes, HTML Code , etc.

Example:-

{

rewardName: "Cabela's eGiftCard $25.00",

shortDescription: '<p>adidas gift cards can be redeemed in over 150 adidas Sport Performance, adidas Originals, or adidas Outlet stores in the US, as well as online at&nbsp;<a href="http://adidas.com/">adidas.com</a>.</p>

terms: '<p>adidas Gift Cards may be redeemed for merchandise on&nbsp;<a href="http://adidas.com/">adidas.com</a>&nbsp;and in adidas Sport Performance, adidas Originals, and adidas Outlet stores in the United States.'

}

SOLUTION

CREATE TABLE `brand` (
`reward_name` varchar(2048),
`short_description` varchar(2048),
`terms` varchar(2048),  
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

While inserting , In followed JSON.stringify()

    let brandDetails= {
    rewardName: JSON.stringify(obj.rewardName),  
    shortDescription: JSON.stringify(obj.shortDescription),
    term: JSON.stringify(obj.term),
     }

Above is the JSON object and below is the SQL Query that insert data into MySQL.

let query = `INSERT INTO brand (reward_name, short_description, terms) 
VALUES (${brandDetails.rewardName}, 
(${brandDetails.shortDescription}, ${brandDetails.terms})`;

Its worked....

enter image description here

Solution 10:[10]

If it is static text, you can use two single quote instead of one as below:

DEC @text = 'Khabir''s Account'

See after Khabir there are two single quote ('')

If your text is not static and it is passed in Store procedure parameter then

REPLACE(@text, '''', '')

Solution 11:[11]

Use a backtick (on the ~ key) instead; 

`O'Brien`

Related Questions