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.
Related Articles
- Escaping special characters other than single quote using STRING_ESCAPE function.
- Read the article on how to encrypt stored procedures, user defined functions and views.
Reference
- Details about LIKE clause in Microsoft Docs.