How do I escape a single quote in SQL Server?

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 .

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

Double quotes option helped me

SET QUOTED_IDENTIFIER OFF;
insert into my_table values("hi, my name's tim.");
SET QUOTED_IDENTIFIER ON;

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,"'","''") + ...etc
SET QUOTED_IDENTIFIER ON;

The STRING_ESCAPE funtion can be used on newer versions of SQL Server

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

This should work: use a back slash and put a double quote

"UPDATE my_table SET row =\"hi, my name's tim.\";