How to Escape a Single Quote in SQL Server?

SQL stands for structured query language and is used to query databases for analytical needs. While using arithmetic queries, some results require strings to help them understand better. Strings can be formed by enclosing text in quotes. However in a case when quotes are themselves required in a string, then we need to escape them. In this article, we are going to see how we can escape a single quote in SQL Server.

Single Quote in SQL Server

Single quote (‘) is a special character used to denote the beginning and end of a string literal. It is used to represent text values in SQL queries and allows developers to work with alphanumeric data. The single quote serves as a delimiter for allowsvalues. When a sequence of characters is enclosed within single quotes, SQL Server interprets it as a string literal. When a string contains special characters or reserved keywords, using single quotes helps SQL Server understand that the content between the quotes is a literal string and not a part of the SQL syntax.

Setting Up Environment

Let us start by creating a table and insert some values in it. Suppose we have the following customer table. The following query creates the customer table and inserts a few records in it.

-- Create the customer table
CREATE TABLE customer (
    id INT,
    name VARCHAR(20)
);

-- Insert sample values into the customer table
INSERT INTO customer VALUES
    ('1', 'John Doe'),
    ('2', 'Jane Smith'),
    ('3', 'Bob Johnson');

The following is the initial data in the table:

Output

Using Quotes in SQL Server

In SQL Server, quotes are used to denote strings. For instance, the following query returns a string:

Query:

--Example: Using quotes to create a string
SELECT ‘String value’

Output:

Output

Explanation: When using quotes, it’s important to note that column names should not be enclosed in quotes, as it results in a constant value in each row:

Quotes select the text in the form of a string. We cannot enclose column names in quotes as it will result in a string with that value instead. See the following example.

Query:

-- Display the contents of the customer table with a constant string
SELECT Name, 'Name' FROM customer;

Output:

Output

Explanation: As we can see the column name when enclosed just returns a constant value in each row.

How to Escape Single Quotes in SQL Server

Sometimes we might need to put quotes inside the text. We can do it in the following two ways.

Method 1: Enclose in the Opposite Set

We can enclose single quotes in double quotes and vice versa. In the following example, we will put single quotes around a value.

Query:

SELECT " 'Name' " FROM customer;

Output:

Output

Explanation: Thus enclosing in double quotes helps to escape single quotes.

Method 2: Write Them Twice Consecutively

In some cases, when you cannot use double quotes, you can escape single quotes inside single quotes by writing them along with each other. Every single quote escapes the next one (except the border ones).

Query:

SELECT '''Name' FROM customer;

Output:

Output

Explanation: As you can see, we enclosed single quotes inside single quotes by writing them consecutively.

Practical Usage

A practical application which signifies the concatenation of columns into a string using the CONCAT function. This real-world example define the significance of handling quotes when combining textual information

A realistic example of using quotes can be combining columns in a string to give information. See the example below.

Query:

SELECT concat(name, '''s id is ', id) AS info FROM customer;

Output:

Output

Explanation: The above example combines the information from two columns into a single info column for easier understanding.

Conclusion

Overall after reading whole article now you have good understanding of how to escape single quote in SQL Server. We have sw some method to escape single quotes which are Enclose in the Opposite Set and Write Them Twice Consecutively with their examples. Now you can easily perform queries and get the output.