SQL Server Escape Single Quote in String

In this SQL Server tutorial, you will learn about SQL Server escape single quote in string with examples.

Sometimes, when you want to insert or find the string containing a single quote through the query, and if you don’t know how to handle the single quote in SQL Server, you usually get the error.

Here, you will understand how to escape a single quote in a string using the double single quote and CHAR() function.

Table of Contents

SQL Server Escape Single Quote in String

When you use the single quote ( ‘ ) in SQL Server, it indicates the beginning and the end of a string literal. But when the single quote is used in the string, it confuses the SQL parser and generates errors or wrong queries.

For example, inserting the string into a table column that contains a single quote raises an error. Let’s understand this with an example.

Create a table CustomerAccount with columns CustomerID, CustomerName, and AccountType using the below query.

CREATE TABLE CustomerAccount (
	CustomerID INT IDENTITY(1,1) PRIMARY KEY,
	CustomerName NVARCHAR(100),
	AccountType NVARCHAR(50)
);

After creating the CustomerAccount table, use the below query to insert the record: customer details, such as customer name ‘Adam’s’, and account type is Saving.

INSERT INTO CustomerAccount (CustomerName, AccountType)
VALUES('Adam's','Saving');

SQL Server Escape Single Quote in String Syntax Error

When you try to insert the customer name as ‘Adam’s’, which contains the single quote before the letter s in that name, you can see SQL parser shows the error message in the output something like Incorrect syntax near ‘s’.

So, to avoid this error ( or escape this single quote), insert the customer name containing the single quote instead of the single quote before the s, and use the double single quote using the below query.

INSERT INTO CustomerAccount (CustomerName, AccountType)
VALUES('Adam''s','Saving');

Now view the CustomerAccount table using the below query.

SELECT * FROM CustomerAccount;

As you can see in the above output, you have successfully inserted the customer name containing the single quote without any error. So replace the single quote with two single quotes in the string wherever you want to use the single quote in the string.

This is how to use the two single quotes in a string in SQL Server escape single quote in string.

SQL Server Escape Single Quote in String using CHAR() Function

The CHAR() function in SQL Server returns the characters based on the specified integer. It can return characters such as A, B, special characters, spaces, etc. So, the single quote is a type of character.

The integer 39 represents the single quote, so when you pass the 39 to the CHAR(39) function, return the single quote. For example, insert the name Jhon E’s Pond in the CustomerAccount table as the customer name.

INSERT INTO CustomerAccount (CustomerName, AccountType)
VALUES('Jhon E'+CHAR(39)+'s Pond','Saving');

Now, view the new customer record using the below query.

SELECT * FROM CustomerAccount;

SQL Server Escape Single Quote in String using CHAR() Function

You can also insert a single quote string using the CHAR(39) from the above output. As you can see, the customer’s name is Jhon E’s Pond.

So here, the string you want to insert containing a single quote is concatenated using the + operator, and it is a way to combine multiple strings (characters) into a single string in SQL Server.

Concatenating two strings and a character using the plus operator, the first is the (‘Jhon E’), the second is the character CHAR(39) representing the single quote, and the third is the string (‘s Pond).

This is how to escape a single quote in a string using the CHAR function in SQL Server. Now you know how to handle the single quote in a string by the above two methods; you can also use both methods to search for any string in your database.

Conclusion

In this SQL Server tutorial, you have learned about two ways, which are the single double quote and the CHAR() function, to insert the string containing the single quote.