How to escape a single quote in SQL Server?

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

ID	Name
1	John’s Pizza
2	Mary’s Cafe
3	Alice’s Diner

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

ID	EscapedName
1	John^s Pizza
2	Mary^s Cafe
3	Alice^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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

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?.