How do I escape a single quote in SQL Server?

In SQL Server, escaping a single quote is essential when you're working with string literals that contain apostrophes or single quotes. Since SQL Server uses single quotes to delimit strings, an unescaped quote inside a string can confuse the SQL parser and throw an error — or worse, open the door for SQL injection.

How to escape a single quote:

To include a single quote inside a string, you simply double it up. That means using two single quotes ('') instead of one.

Example:

  SELECT 'It''s a great day!';

This will return:

  It's a great day!

Use cases:

When inserting names like O'Reilly:

  INSERT INTO Authors (Name) VALUES ('O''Reilly');

When generating dynamic SQL:

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM Users WHERE LastName = ''O''Reilly'''
EXEC sp_executesql @sql

Why it's important:

• Avoid syntax errors: SQL Server expects properly formed strings.
  
• Prevent SQL Injection: If user input includes quotes and you don’t escape them properly, attackers can manipulate the query.
  

Bonus Tip:

• If you're using programming languages like Python, C#, or Java with SQL, use parameterized queries instead of string concatenation — it's safer and handles escaping for you.
  
• So, whenever you need to include a single quote inside a string in SQL Server, just remember: two single quotes equals one literal quote.