FB

Do you need to escape special characters or escape a single quote in SQL? Learn how to do that in this article.

What Is Escaping?

When you are working with text values in Oracle SQL, you use single quote characters.How to Escape Single Quotes in SQL

This could be when you’re selecting them:

SELECT 'Database Star'
FROM dual;

Or, when you’re inserting them:

INSERT INTO customer (id, customer_name)
VALUES (45, 'ABC Company');

The single quote character indicates the start and end position of your string.

But what if you want to have a single quote as part of your string?

For example:

SELECT 'O'Reilly'
FROM dual;

This would cause an issue, because there are three single quotes. Oracle doesn’t know where your string should end.

So, to allow values within single quotes (and some other special characters) to be used within a string, you need to “escape” them.

Escaping a character is where you say to the database, “Hey, this character here is part of my string, don’t treat it as a special character like you normally would”.

There are a few SQL escape single quote methods that I’ll cover in this article.

Get My SQL Cheat Sheets

SQL Cheat Sheets for Oracle, SQL Server, MySQL, and PostgreSQL. Save time writing SQL with these PDF reference files.

 

Use Two Single Quotes For Every One Quote To Display

The simplest method to escape single quotes in Oracle SQL is to use two single quotes. For example, if you wanted to show the value O’Reilly, you would use two quotes in the middle instead of one.

The single quote is the escape character in Oracle SQL.

SELECT 'O''Reilly' AS quoted_string
FROM dual;
QUOTED_STRING
O’Reilly

If you want to use more than one in a string, you can.

SELECT 'Let''s go to that ''place''' AS quoted_string
FROM dual;
QUOTED_STRING
Let’s go to that ‘place’

 

Use Literal Quoting

Another SQL escape single quote method you can use is “literal quoting”.

This means you can put the letter “q” in front, followed by your escape character, then square brackets.

For example:

SELECT q'[O'Reilly]' AS quoted_string
FROM dual;
QUOTED_STRING
O’Reilly

This means that any quotes inside the square brackets are not escaped. The output string appears exactly as you have entered it.

Another example:

SELECT q'[The 'end' of the day]' AS quoted_string
FROM dual;
QUOTED_STRING
The ‘end’ of the day

You can still do this with multiple single quotes as well.

 

SELECT q'[This is an ''example'' of two single quotes]' AS quoted_string
FROM dual;
QUOTED_STRING
This is an ”example” of two single quotes

 

Use the CHR Function

Another method is to use the CHR function.

The CHR function returns a character from the specified ASCII code.

CHR(39) returns a single quote, which can be concatenated to your string.

For example:

SELECT 'O' || CHR(39) || 'Reilly' AS quoted_string
FROM dual;
QUOTED_STRING
O’Reilly

You can use this in multiple places too.

SELECT 'The ' || CHR(39) || 'end' || CHR(39) || ' of the day' AS quoted_string
FROM dual;
QUOTED_STRING
The ‘end’ of the day

 

Escaping SQL Wildard Characters

When you’re using the LIKE keyword, you supply wildcard characters.

The % symbol represents zero or more characters, and the _ symbol represents one character.

What if you wanted those characters to be a part of your string and not a wildcard character?

You can do that as part of the LIKE keyword, by specifying the ESCAPE keyword.

SELECT customer_name
FROM customer
WHERE customer_name LIKE '%\_%' ESCAPE '\';

This query looks for all customer_name values that contain an underscore.

Normally, the underscore indicates a single character wildcard, but I have specified the ESCAPE ‘\’ keyword here. This means that the \ character is used as an escape character, which forces the _ to be used literally and not as a wildcard.

We can specify a different escape character if needed.

SELECT customer_name
FROM customer
WHERE customer_name LIKE '%/_%' ESCAPE '/';

This performs the same query, just using a different escape character.

We can also escape a % symbol.

SELECT customer_name
FROM customer
WHERE customer_name LIKE '\%%' ESCAPE '\';

This query finds all customer_name values that start with a % symbol.

 

So, that’s how you can escape single quotes in SQL. There are a few methods, so you can use whichever method you prefer.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheets Now: