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

User · Answer

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

User · Answer

You just have to double up on the single quotes     insert into Person  First  Last  values   Joe    O  Brien

User · Answer

use double quotation marks around the values   insert into Person  First  Last  Values  Joe   O Brien

User · Answer

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

User · Answer

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   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   It works for other escape characters too  For example loading a Regex pattern value  i e        P N              lt   gt   d d               i  in not in   -i                                   into a table

User · Answer

Use a backtick  on the   key  instead     O Brien

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-update

Examples related to sql-insert