Escape Single Quote In String In SQL Server

In T-SQL or simple SQL query in SQL Server, you should be careful in using single quote in strings. There are many instance, where you need single quote in strings. In such cases, you have to escape single quote to avoid any errors. There are several ways to escape a single quote. Below are couple of methods.

Escape Single Quote Using Another Single Quote

The easiest way to escape single quote in a string to double up the quote. Add another single quote to the quote. Here is an example.

DECLARE @MyTecBits_Table TABLE (
[name] VARCHAR(100)
)

INSERT INTO @MyTecBits_Table VALUES ('Hello, it''s Sam.')

SELECT name FROM @MyTecBits_Table
GO

Result:

name
--------------------
Hello, it's Sam.

Using QUOTED_IDENTIFIER

Another crude method is to use QUOTED_IDENTIFIER. When QUOTED_IDENTIFIER is set to OFF, the strings can be enclosed in double quote. In this case, you don’t need to escape the single quote. This method will be helpful when you have to use lot of string value with single quotes to be used with the SQL query. Here is the example.

DECLARE @MyTecBits_Table TABLE (
    [name] VARCHAR(100)
)

SET QUOTED_IDENTIFIER OFF;
INSERT INTO @MyTecBits_table VALUES ("Hello, it's Tom.")
SET QUOTED_IDENTIFIER ON;

SELECT name FROM @MyTecBits_table

Result:

name
----------------------
Hello, it's Tom.

Escape Single Quote

Related Articles

Reference

Related Articles & Tools

Leave your thoughts...