Answer: In SQL Server, you can escape a single quote (‘) by doubling it (‘ ‘).
When working with SQL Server, handling special characters like single quotes in string values can be tricky. Since single quotes are used to delimit string literals, extra single quotes can be escaped to avoid syntax errors. In this blog, let us explore how this can be done.
Table of Contents:
Single Quote in SQL Server
A single quote ( ‘ ) denotes a string literal in SQL Server. If you need to include the single quote in string literals, it can be done by simply doubling it ( ‘ ‘ ).
Now let us explore how single quotes can be used inside the string.
Methods to Escape a Single Quote in SQL Server
First, let’s create a Company table with Company_location and Details columns and use it for all the examples.
# Create a Company table
CREATE TABLE Company (
Company_location varchar(20),
Details VARCHAR(500)
);
-- Insert some values into the company table
INSERT INTO Company (Company_location, Details) VALUES
('Bangalore', 'Intellipaat''s real-world projects ensure hands-on experience in Artificial Intelligence.'),
('Bangalore', 'Intellipaat''s courses cover trending technologies and skills like Cyber Security.'),
('Bangalore', 'Intellipaat''s Data Science course provides a globally recognized certificate.');
-- To display the company table
SELECT * FROM Company;
This is how the table looks when the values are inserted into it.
There are a few methods in SQL Server to escape a single quote. Let us explore each method in detail.
Method 1: Inserting Data with Single Quotes in SQL Server
The single quotes can be inserted into the table by simply doubling the quote.
Example:
INSERT INTO Company (Details)
VALUES ('Intellipaat’'s courses cover trending technologies and skills like Cyber Security.');
--To display the table
select * from Company;
Output:
Explanation: Here the apostrophe in “Intellipaat’s” is escaped using double single quotes (‘’).
Method 2: Using Single Quotes in SELECT Statement in SQL Server
The single quotes can be used in the select statement as well. Using the SELECT statement in SQL Server allows you to represent single literals and escape special characters within queries.
Example:
SELECT 'Intellipaat''s real-world projects ensure hands-on experience in Artificial Intelligence.';
Output:
Explanation: The apostrophe in “Intellipaat’s” is escaped using double single quotes, which is required in SQL to avoid syntax errors.
Method 3: Using Single Quotes in WHERE Clause in SQL Server
In SQL Server, when using a single quote inside a string in a WHERE clause, we can escape the single quotes by doubling it (”) to avoid syntax errors.
Example:
SELECT * FROM Company WHERE Details = 'Intellipaat''s courses cover trending technologies and skills like Cyber Security.';
Output:
Explanation: Filters the results to only return rows where the details column exactly matches the given string. Since the given string is “Intellipaat”s courses cover trending technologies and skills like Cyber Security” this will retrieve the rows with this string.
Method 4: Using UNICODE (N’ ‘) to Escape Single Quotes in SQL SERVER
In SQL Server, the N’ ‘ prefix is used to specify UNICODE strings (for NVARCHAR, NCHAR data types). We still need to double the single quotes inside the string.
Example:
SELECT N'Intellipaat''s Data Science course provides a globally recognized certificate.';
Output:
Explanation: The N before the string indicates that the text is stored as UNICODE. Two single quotes are used to escape a single quote inside the string.
Method 5: Using Quoted Identifiers ([ ]) in SQL Server
In SQL Server, we can use square brackets [ ] to escape reserved keywords or special characters in table or column names, but not for escaping string literals.
Example:
SELECT [Details] FROM Company;
Output:
Explanation: This Quoted identifier ([ ]) will return all the values of the field Details from the company table. Thus the select retrieves all the rows from the company table.
Method 6: Using CONCAT() and CHAR(39) (ASCII code for Single Quote) in SQL Server
In SQL Server, the concat function is used to combine multiple string values into a single string. Since a single quote is used to enclose string literals, CHAR(39) helps in inserting it into the strings.
Example:
SELECT CONCAT('Intellipaat', CHAR(39), 's AI course helps professionals upskill.') AS [Details];
Output:
Explanation: The CONCAT() function joins multiple string values together, and CHAR(39) is used to represent a single quote.
Method 7: Using QUOTENAME() in SQL Server
Quotename() is mainly used for escaping database object names like table or column names by enclosing them in square brackets ( [ ] ) or double quotes ( “ ).
Example:
SELECT QUOTENAME('Intellipaat''s Data Science course', CHAR(39)) AS QuotedText;
Output:
Explanation: QUOTENAME() adds delimiters around a given string. It ensures that the identifiers are safely enclosed to avoid syntax errors and escape the single quote by doubling it.
Method 8: Using REPLACE() in SQL Server
When working with dynamic text inputs that may contain single quotes (‘), using REPLACE() helps prevent SQL errors by doubling each single quote (”).
Example:
SELECT REPLACE('Intellipaat''s Data Science course is recognized.', CHAR(39), '') AS NoQuotes;
Output:
Explanation:
This REPLACE () is used to find and replace occurrences of a specific substring within a given text. The apostrophe (‘) in “Intellipaat’s” gets removed, making it “Intellipaats”.
Method 9: Using Double Single Quotes in SQL Server
In SQL Server, double single quotes(‘ ‘) are used to escape a single quote ( ‘ ) inside a string literal.
Example:
SELECT 'Intellipaat''s Data Science course provides a globally recognized certificate.';
Output:
Explanation:
Here, we can escape the single quote in the string ‘Intellipaat’’s’ using the double single quote which will return Intellipaat’s as an output.
Method 10: Using STRING_ESCAPE in SQL Server
The STRING_ESCAPE escapes the single quote ( ‘ ) properly for safe storage in the SQL Server.
Example:
SELECT * FROM Company
WHERE Details = STRING_ESCAPE('Intellipaat''s Data Science course provides a globally recognized certificate.', 'json');
Output:
Explanation: The JSON mode ensures that the characters like single quotes and backslashes are properly escaped in the given string.
Method 11: Using XML path in SQL Server
In SQL Server, the XML path technique is often used to concatenate strings efficiently while handling single quotes correctly.
Example:
SET QUOTED_IDENTIFIER ON;
GO
DECLARE @str NVARCHAR(MAX) = 'Intellipaat''s real-world projects ensure hands-on experience in Artificial Intelligence.';
SELECT (SELECT @str FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS Result;
Output:
Explanation:
- The DECLARE statement declares a variable @str of type NVARCHAR(max) which assigns the string to it.
- The value function is used to extract and convert the XML output back into normal text.
| Method | Use Case | Pros | Cons |
| Double Single Quotes ( ‘ ‘ ) | General Escaping | Simple and widely used, which works well in INSERT, SELECT, etc. | Can be hard to read complex queries |
| UNICODE ( N ‘…’ ) | Unicode data (NVARCHAR) | Supports Multilingual characters | Not needed for Non-Unicode data |
| CONCAT() & CHAR(39) | For building dynamic strings | Helps to avoid syntax errors in dynamic SQL | More complex than simple escaping |
| QUOTENAME() | Safe escaping in queries | Prevents error with reserved keywords | By default, it adds [ ] for identifiers, not ‘ ‘ |
| REPLACE () | For handling dynamic text | Useful for replacing single quotes dynamically | Can modify unintended parts of the string |
| Quoted Identifiers ([ ]) | Handling reserved keywords or special characters in identifiers. | Avoid syntax issues when table names have spaces or reserved words | Only for identifiers, not for string literals |
| STRING_ESCAPE() | Escaping special characters for JSON format | Best for working with JSON data | Limited to JSON mode only |
Real-world Examples
1. Storing Customer Feedback in a database: A customer leaves a review that includes an apostrophe( ‘ ).
Example:
--Create a Customer reviews table
CREATE TABLE Customer_Reviews (
ReviewID INT IDENTITY(1,1) PRIMARY KEY,
ReviewText NVARCHAR(500)
);
-- Insert data with a single quote
INSERT INTO Customer_Reviews (ReviewText)
VALUES ('The pizza''s taste was amazing!');
--To display the data
SELECT * FROM Customer_Reviews;
Output:
Explanation:
- To insert an actual single quote inside the text, you must escape it by doubling it.
- The SELECT statement retrieves all rows from the Customer_reviews table.
2. E-Commerce Database: A user searches for a product like “Men’s Shoes” in an e-commerce database.
Example:
--Create a Customer_reviews table
CREATE TABLE Customer_Reviews (
ReviewID INT IDENTITY(1,1) PRIMARY KEY,
ReviewText NVARCHAR(500)
);
--Inserting data with a single quotes
INSERT INTO Customer_Reviews (ReviewText)
VALUES
('The pizza''s taste was amazing!'),
('Can''t believe how good the service was!'),
('It''s the best coffee I''ve ever had.');
--To display data
SELECT * FROM Customer_Reviews;
Output:
Explanation:
- NVARCHAR stores customer reviews as Unicode text. The review text contains an apostrophe ( ‘ ), e.g ., “Pizza’s”, “Can’t”, and “It’s”.
- To insert an actual single quote inside the text, you must escape it by doubling it.
Conclusion
In static queries, using double single quotes (”) is enough to escape single quotes. However, for dynamic SQL and user-generated inputs, QUOTENAME(), REPLACE(), and CHAR(39) are the best choices for secure and error-free execution. Understanding these approaches helps you to efficiently escape the single quote in SQL Server.
FAQs
1. Why do we need to escape single quotes in SQL Server?
Single quotes are used for string literals, so escaping prevents syntax errors when inserting or querying text with apostrophes.
2. How do I escape a single quote in a string?
Use two single quotes (”) to represent one inside a string, like ‘It”s a great day!’.
3. Can I use a function to handle single quotes automatically?
Yes, functions like REPLACE() or QUOTENAME() can be used to escape or properly format string values.