SQL Escape Single Quote

“Welcome to the world of SQL. In this post, we will learn how to escape a single quote character in SQL.”

Let’s get exploring.

What is a Single Quote Character?

In SQL or development, a single quote character denotes a literal string or a set of commands.

When working with SQL databases, you often use single quote characters to enclose a string type.

For example, when inserting a string value in a table, you enclose them in single quotes as shown:

insert into table_name(col1, col2) values (1, 'string')

You can also use single quote characters in a native SQL command as shown:

select 'column_name';

Therefore, learning how to use single quote characters in SQL is essential.

Two single quote characters denote the start and end positions of a string literal.

What happens if we wish to include a single character as part of a string? For example, we could insert a string with an apostrophe.

Consider an example below:

insert into table_name(col1, col2) values (1, 'Apple's Bar')

The above statement will prove a challenge to the database. This is because there is no indication of where the string should start or end.

This is where escape characters come into play.

What is an Escape Character?

An escape character is a special character that tells SQL to treat another character, such as a single quote, as part of a string literal.

Therefore, to include a single quote as part of a string, we use an escape character to tell SQL that the specific single quote is part of the string.

Let us explore the various methods of escape characters.

Method 1 – Use Double Single Quotes

The simplest and most applicable form of escaping a single character is to use double single quotes.

This method of escape is supported in major SQL database engines such as MySQL, PostgreSQL, Oracle, and MS SQL Server.

This method works by adding a single quote before the single quote you wish to escape.

Confusing? Consider the example below:

SELECT 'Adam''s Book' as a result;

Our target string is ‘Adam’s Book’ in the above example. Hence, to include the single quote as part of the string with no errors, we add one more single quote.

The resulting value is as:

+-------------+

| result |

+-------------+

| Adam's Book |

+-------------+

Thankfully, this method is widely adopted by major database engines. You can also use more than one single quote character in one command as:

SELECT 'Adam''s Book was at Rachael''s House' as a result;

The above query returns the resulting string as shown:

+------------------------------------+

| result |

+------------------------------------+

| Adam's Book was at Rachael's House |

+------------------------------------+

You can see that we are able to include multiple single quote characters in a string literal.

Method 2 – Using Backslash Characters

In MySQL, you can use backslash characters to escape a single quote character. However, this technique is not widely adopted in other database engines.

Example:

mysql> SELECT 'Adam\'s House' as a result;

The statement above should return:

Method 3 – Double Quotes

You can use double quotes in some database engines to enclose a string. In that case, we can use double quotes to enclose a string that contains single quote characters.

Take the example shown below:

SELECT "Adam's House" as a result;

Unfortunately, this method is not supported by all database engines and may result in an error.

Method 4 – Using the Char Function

In SQL Server and Oracle, you can use the char() function and string concatenation to add a single quote character.

The function allows you to specify an integer value, and it will return its ASCII character equivalent.

We know that the ASCII code for a single quote is 39. Hence, we can provide this value in a string as shown:

SELECT 'Adam'+CHAR(39)+'s House'

The statement above should return the string:

Keep in mind that the above syntax is only supported in SQL Server and Oracle Databases.

Closing

That’s all for this one. In this article, we provided you with four methods you can use to escape a single quote character in SQL. Choose the one that works for you.

Thanks for reading!!