How do I escape a single quote in SQL Server?
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_tableResults
value==================hi, my name's tim.
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
How about:
insert into my_table values('hi, my name' + char(39) + 's tim.')