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?


escape special characters in oracle 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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • rekha

    This article will help me a lot.
    thank you.

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)