How to escape a single quote in SQL Server?

Let's look at how to escape a single quote in SQL Server, with a solution that is also reproducible in MySQL

By Prabhakaran S  ·  September 2023

Problem

How to escape a single quote in SQL Server, with a solution that is also reproducible in MySQL?

For example, let’s try to replace the single quote character with a hat (^) character.

Input

IDName
1John’s Pizza
2Mary’s Cafe
3Alice’s Diner

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

IDEscapedName
1John^s Pizza
2Mary^s Cafe
3Alice^s Diner

Solution:

To escape a single quote in SQL Server, you can use a double single quote (”) to represent a single quote. This is specific to SQL Server.

However, if you want a solution that works in both SQL Server and MySQL, you can use the REPLACE function.

SELECT ID, REPLACE(Name, '''', '^') AS EscapedName
FROM ExampleTable;

Explanation:

In this query, we use the REPLACE function to replace each single quote (‘) with two single quotes (”).

In SQL Server, escaping a single quote is done by using two consecutive single quotes (”) to represent a single quote within a string.

In MySQL, you can also escape a single quote using the backslash (). However, to create a solution that works in both SQL Server and MySQL, we used the REPLACE function to replace single quotes with double single quotes (”).

This is because REPLACE is supported in both database systems, making the solution portable across different database platforms.

  1. SQL for Data Science – Level 1
  2. SQL for Data Science – Level 2
  3. SQL for Data Science – Level 3
  1. Introduction to SQL
  2. SQL Window Functons – Made Simple and Easy
  3. SQL Subquery

More SQL Questions

  1. How to efficiently convert rows to columns in SQL?
  2. How to transpose columns to rows in SQL?
  3. How to select first row in each GROUP BY group?
  4. How to get a random row in SQL?.