How to Escape Special Characters in Oracle SQL queries

Whether you are working as PL/SQL developer or ETL developer or Data Warehousing Developer or using SQL and PL/SQL, you must know about how to escape special characters in SQL queries.

In the article, you will learn:

1) How to escape ampersand (&) characters in SQL?

2) How to escape wildcard characters in SQL?

3) How to escape quotes in SQL?

Let’s start with first one.

1) Escape ampersand (&) characters in SQL:

Login to SQL*Plus and issue SET DEFINE ~ command. This command will change the setting and allow &’s (ampersands) to be used in text.

C:\>sqlplus scott/tiger@xe

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 10 16:45:14 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> SELECT 'harry potter & hermione granger' FROM DUAL;

Enter value for hermione: AND
old   1: SELECT 'harry potter & hermione granger' FROM DUAL
new   1: SELECT 'harry potter AND granger' FROM DUAL

'HARRYPOTTERANDGRANGER'
------------------------
harry potter AND granger

Here you can see when we enter & between two text, it asks for the value but when we change the setting it allows & in SQL.

SQL> SET DEFINE ~

SQL> SELECT 'harry potter & hermione granger' FROM DUAL;

'HARRYPOTTER&HERMIONEGRANGER'
-------------------------------
harry potter & hermione granger

SQL>

You can use below method as well:

SQL> SELECT 'harry potter \& hermione granger' FROM DUAL;

Enter value for hermione:
old   1: SELECT 'harry potter \& hermione granger' FROM DUAL
new   1: SELECT 'harry potter \ granger' FROM DUAL

'HARRYPOTTER\GRANGER'
----------------------
harry potter \ granger

You need to define an escape character.

SQL> SET ESCAPE '\'

SQL> SELECT 'harry potter \& hermione granger' FROM DUAL;

'HARRYPOTTER&HERMIONEGRANGER'
-------------------------------
harry potter & hermione granger

You can use one more method by scanning off:

SQL> SELECT 'harry potter & hermione granger' FROM DUAL;

Enter value for hermione:
old   1: SELECT 'harry potter & hermione granger' FROM DUAL
new   1: SELECT 'harry potter  granger' FROM DUAL

'HARRYPOTTERGRANGER'
---------------------
harry potter  granger

Here you need not to scan for substitution variables.

SQL> SET SCAN OFF

SQL> SELECT 'harry potter & hermione granger' FROM DUAL;

'HARRYPOTTER&HERMIONEGRANGER'
-------------------------------
harry potter & hermione granger

SQL>

You can use another best way to escape the & without using SET commands. Use concatenation to escape the & in your SQL queries.

SQL> SELECT 'harry potter' || ' & ' || 'hermione granger' FROM DUAL;

'HARRYPOTTER'||'&'||'HERMIONEGR
-------------------------------
harry potter & hermione granger

SQL>

2) Escape wildcard characters in SQL:

As we know that LIKE keyword is a pattern matching keyword. Character ‘_’ is a wild card character that is used to match exactly one character in a string while ‘%’ is used to match zero or more occurrences.

Let’s know how to escape these characters in SQL.

SQL> SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_ID LIKE '%/_%' ESCAPE '/';

SQL> SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_ID LIKE '%\%%' ESCAPE '\';

I hope you understood how to Escape wildcard characters in SQL.

3) Escape quotes in SQL:

This is really a simple way to escape quotes in SQL. Use two quotes to display every single quote.

 
SQL> SELECT 'harry potter''s birthday' AS QUOTE_S FROM DUAL;

QUOTE_S 
-----------------------
harry potter's birthday

SQL> SELECT 'harry potter''s birthday ''blast'' today' AS SINGLE_QUOTE FROM DUAL;

SINGLE_QUOTE 
-------------------------------------
harry potter's birthday 'blast' today

SQL> SELECT 'harry potter''s birthday ''''blast'''' today' AS Double_Quote FROM DUAL;

DOUBLE_QUOTE
---------------------------------------
harry potter's birthday ''blast'' today

You can use Q expression to get all above results. The examples are shown below.

SQL> SELECT q'[Harry Potter's Birthday]' AS QUOTE_S FROM DUAL;

QUOTE_S
-----------------------
Harry Potter's Birthday

SQL> SELECT q'[Harry Potter's Birthday 'Blast' Today]' AS SINGLE_QUOTE FROM DUAL;

SINGLE_QUOTE
-------------------------------------
Harry Potter's Birthday 'Blast' Today

SQL> SELECT q'[Harry Potter's Birthday ''Blast'' Today ]' AS Double_Quote FROM DUAL;

DOUBLE_QUOTE
----------------------------------------
Harry Potter's Birthday ''Blast'' Today

I hope this article will help you to escape special characters in Oracle SQL queries.