How do I escape a single quote in SQL Server?

To include a literal single quote ' inside a string in SQL Server, you can escape it by using two single quotes ''. When the query is parsed, each pair of single quotes is treated as one literal quote character.

Basic Example

If you need to insert the word O'Neill into a table, you’d write it as:

INSERT INTO Employees (LastName)
VALUES ('O''Neill');

When SQL Server processes O''Neill, it interprets it as O'Neill in the inserted data.

Practical Scenarios

Simple String Literals
```
SELECT 'It''s a good day';
```
Returns: It's a good day
Constructing Dynamic SQL
When building a dynamic SQL statement in T-SQL, you need to escape quotes in strings:
```
DECLARE @sql NVARCHAR(200);
SET @sql = N'SELECT * FROM Employees WHERE LastName = ''O''Neill''';
EXEC(@sql);
```
Here, 'O''Neill' represents O'Neill in the final query.
Using Parameters
In most applications, you’d rely on parameterized queries to avoid directly handling string escapes in dynamic SQL. For example:
```
DECLARE @LastName NVARCHAR(50) = 'O''Neill';
SELECT * FROM Employees
WHERE LastName = @LastName;
```
With parameters, the database engine handles escaping internally.

Summary

To escape a single quote, use two single quotes in a row ('').
Always be mindful of where and how you’re inserting the escaped quotes—especially in dynamic SQL—to prevent syntax errors or SQL injection risks.
When possible, use parameterized queries so you don’t have to manually escape quotes in strings.

Recommended Courses

CONTRIBUTOR

TechGrind