How do I escape a single quote in SQL Server?

ID : 1125

viewed : 108

Tags : sqlsql-servertsqldelimitersingle-quotessql

Top 5 Answer for How do I escape a single quote in SQL Server?

vote vote

98

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

89

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

76

How about:

insert into my_table values('hi, my name' + char(39) + 's tim.') 
vote vote

61

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

Doubling the Single Quotes Method

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

UNICODE Characters

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; 

QUOTE_IDENTIFIER

CONCLUSION

The above mentioned methods are applicable to both AZURE and On Premises .

vote vote

58

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.");

Top 3 video Explaining How do I escape a single quote in SQL Server?

Related QUESTION?