How do I escape a single quote in SQL Server

Question

I m trying to insert some text data into a table in SQL Server 9   The text includes a single quote      How do I escape that   I tried using two single quotes  but it threw me some errors   eg  insert into my table values  hi  my name  s tim

User · Answer

This should work  use a back slash and put a double quote   UPDATE my table SET row    hi  my name s tim

User · Answer

Many of us know that the Popular Method of Escaping Single Quotes is by Doubling them up easily like below   PRINT  It  s me  Arul        we are going to look on some other alternate ways of escaping the single quotes   1 UNICODE Characters  39 is the UNICODE character of Single Quote  So we can use it like below   PRINT  Hi it  CHAR 39   s Arul    PRINT  Helo it  NCHAR 39   s Arul        2 QUOTED IDENTIFIER  Another simple and best alternate solution is to use QUOTED IDENTIFIER  When QUOTED IDENTIFIER is set to OFF  the strings can be enclosed in double quotes  In this scenario  we don   t need to escape single quotes  So this way would be very helpful while using lot of string values with single quotes  It will be very much helpful while using so many lines of INSERT UPDATE scripts where column values having single quotes   SET QUOTED IDENTIFIER OFF  PRINT  It s Arul   SET QUOTED IDENTIFIER ON      CONCLUSION  The above mentioned methods are applicable to both AZURE and On Premises

User · Answer

Also another thing to be careful of is whether or not it is really stored as a classic ASCII    ASCII 27  or Unicode 2019  which looks similar  but not the same   This isn t a big deal on inserts  but it can mean the world on selects and updates   If it s the unicode value then escaping the   in a WHERE clause  e g where blah    Workers  s Comp   will return like the value you are searching for isn t there if the   in  Worker s Comp  is actually the unicode value If your client application supports free-key  as well as copy and paste based input  it could be Unicode in some rows  and ASCII in others  A simple way to confirm this is by doing some kind of open ended query that will bring back the value you are searching for  and then copy and paste that into notepad   or some other unicode supporting editor  The differing appearance between the ascii value and the unicode one should be obvious to the eyes  but if you lean towards the anal  it will show up as 27  ascii  or 92  unicode  in a hex editor

User · Answer

The doubling up of the quote should have worked  so it s peculiar that it didn t work for you  however  an alternative is using double quote characters  instead of single ones  around the string   I e    insert into my table values  hi  my name s tim

User · Answer

I had the same problem  but mine was not based of static data in the SQL code itself  but from values in the data  This code lists all the columns names and data types in my database  SELECT DISTINCT QUOTENAME COLUMN NAME  DATA TYPE FROM INFORMATION SCHEMA COLUMNS  But some column names actually have a single-quote embedded in the name of the column   such as      MyTable   LEOS DATACOLUMN   To process these  I had to use the REPLACE function along with the suggested  QUOTED IDENTIFIER setting  Otherwise it would be a syntax error  when the column is used in a dynamic SQL  SET QUOTED IDENTIFIER OFF      SET  sql    SELECT DISTINCT        TableName             REPLACE  ColumnName  quot   quot   quot    quot        etc SET QUOTED IDENTIFIER ON

User · Answer

Just insert a   before anything to be inserted  It will be like a escape character in sqlServer  Example   When you have a field as  I m fine  you can do  UPDATE my table SET row   I  m fine

User · Answer

The following syntax will escape you ONLY ONE quotation mark   SELECT        The result will be  a single quote  Might be very helpful for creating dynamic SQL

User · Answer

Double quotes option helped me SET QUOTED IDENTIFIER OFF  insert into my table values  quot hi  my name s tim  quot    SET QUOTED IDENTIFIER ON

User · Answer

Single quotes are escaped by doubling them up  just as you ve shown us in your example  The following SQL illustrates this functionality  I tested it on SQL Server 2008   DECLARE  my table TABLE        value  VARCHAR 200     INSERT INTO  my table VALUES   hi  my name  s tim     SELECT   FROM  my table   Results  value                    hi  my name s tim

User · Answer

If escaping your single quote with another single quote isn t working for you  like it didn t for one of my recent REPLACE   queries   you can use SET QUOTED IDENTIFIER OFF before your query  then SET QUOTED IDENTIFIER ON after your query   For example  SET QUOTED IDENTIFIER OFF   UPDATE TABLE SET NAME   REPLACE NAME    S    S     SET QUOTED IDENTIFIER ON  -- set OFF then ON again

User · Answer

How about   insert into my table values  hi  my name    char 39     s tim

User · Answer

This should work  DECLARE  singleQuote CHAR  SET  singleQuote    CHAR 39   insert into my table values  hi  my name    singleQuote   s tim

User · Answer

2 ways to work around this     for   you can simply double it in the string  e g   select  I  m happpy  -- will get  I m happy    For any charactor you are not sure of  in sql server you can get any char s unicode by select unicode       you keep the number   So this case you can also select  I  nchar 39   m happpy

[sql] How do I escape a single quote in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to delimiter

Examples related to single-quotes