SQL SERVER – Escape Single Quotes

When writing SQL statements in SQL Server, you may sometimes need to use literal string values that contain single quotes. Because single quotes denote string literals, including one inside the string can cause errors or unexpected behavior. In this post, I’ll cover a few methods you can use to escape single quotes properly in SQL Server.

The Problem

Consider the following invalid SQL statement:

SELECT *
FROM Customers
WHERE LastName = 'O'Connor'

This statement will fail because the single quote before O’Connor prematurely terminates the string literal. SQL Server now sees O as a string literal and Connor as an invalid column identifier.

We need a way to escape single quotes that appear within string literals to ensure they are treated as valid characters instead of terminating the string.

Method 1: Double Single Quotes

The simplest method is to double the single quote inside the string:

WHERE LastName = 'O''Connor'

By using two single quotes together, SQL Server will interpret them as a literal single quote character instead of terminating the string.

This is the easiest way to escape single quotes but can become difficult to read with multiple escaped quotes.

Method 2: CHAR Function

Another method is to convert the single quote to its underlying ASCII code using the CHAR function:

WHERE LastName = 'O' + CHAR(39) + 'Connor'

The CHAR(39) inserts the ASCII character code 39, translating to a single quote.

This method adds some extra text but can be useful for readability with multiple escaped single quotes in larger strings.

Let me know if you have any other SQL Server string escaping issues. I’m happy to write more technical SQL posts explaining concepts like this.

You can watch my YouTube videos over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)