How do I escape a single quote in SQL Server?

Handling abundant single quotes

Whenever dealing with data brimming with single quotes, it's good to harness the power of the REPLACE() function. It replaces all single quotes in a column with double single quotes, effectively handling your SQL escape needs:

-- Handling those tricky apostrophes
UPDATE addresses
SET street = REPLACE(street, '''', '''''')
WHERE street LIKE '%''%';


A smooth approach for dynamic SQL

Dynamic SQL statements may require concatenation of several text strings. Fortunately, using CHAR(39) ensures that you can define your SQL strings and escape single quotes in a fuss-free factor:

-- CHAR(39) - the secret ingredient in grandma's SQL recipe
DECLARE @DynamicSQL nvarchar(1000);
SET @DynamicSQL = 'SELECT * FROM menu WHERE dish_name = ' + CHAR(39) + 'Fish n'' Chips' + CHAR(39) + ';';
EXEC sp_executesql @DynamicSQL;


Readability matters

Maintaining the readability of SQL scripts is important. By using SET QUOTED_IDENTIFIER ON, you can keep your scripts clean and clear. Remember to set it off again to avoid disrupting other SQL statements:

-- Who said you can't use double quotes in SQL? 
SET QUOTED_IDENTIFIER ON;
SELECT *
FROM authors
WHERE surname = "O'Brien";
SET QUOTED_IDENTIFIER OFF;


Key methods for unique scenarios

Playing the Unicode game

You might need to prefer or need to use Unicode numbers to encode a single quote, particularly when dealing with multilingual content:

-- Be Unicode, my friend
SELECT 'Paris' + NCHAR(39) + 's Eiffel Tower';


Print statements with character

Need to print statements in SQL with embedded quotes? Use concatenation:

-- In SQL, CHAR(39) is worth a thousand words
PRINT 'Priority' + CHAR(39) + 's high.';


These methods guarantee an output without causing any errors.

Taming double quotes beasts

In some scenarios, you can use double quotes for string literals:

-- Giving a free pass to the double quotes
SET QUOTED_IDENTIFIER OFF;
EXEC('SELECT * FROM boroughs WHERE name = "King''s Landing"');
SET QUOTED_IDENTIFIER ON;


Makes SQL commands clearer, but handle with care since it can affect other SQL statements or database settings.