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

eduffy's idea to add a backtick is a good one; it's just backward in their provided code example. Either in JavaScript or in SQLite, you can replace the apostrophe with the accent symbol.

In eduffy's answer, the accent symbol is (presumably accidentally) placed 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.

Answer 2

the solution provided is not working fine, since it ads the string with two single quote in database, the simplest way is to use anti back slash before the apostrophe (single quote).

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

use double quotation marks around the values.

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

Answer 8

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

Answer 9

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.

Answer 10

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'

Answer 11

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.

Answer 12

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

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

Answer 13

Use a backtick (on the ~ key) instead;

`O'Brien`