Escape Sequence in Oracle

Escape special characters when writing SQL queries

--to include single '

SELECT 'Steven's salary is more than 50k INR' AS "SAL_DETAILS" 
FROM Dual;

ORA-01756 : quoted string not properly terminated
 

SELECT 'Steven''s salary is more than 50k INR' AS "SAL_DETAILS" 
FROM   Dual;

SAL_DETAILS
---------------------------------------
Steven's salary is more than 50k INR


--to include double '



SELECT 'You can print double quot ('''') in oracle' "Info"  
FROM Dual;

Info
---------------------------------------
You can print double quot ('') in oracle


SELECT q'[some test ' some test ' some text ']' AS "In 10g" 
FROM dual;

In 10g
------------------------------------
some test ' some test ' some text '



--Escape wild card characters ( _ and % )

           The LIKE keyword allows for string searches.
           The '_' wild card character is used to match exactly one character
           While '%' is used to match zero or more occurrences of any characters.
           These characters can be escaped in SQL as follows.
          
WITH mail_ids AS
   (
     SELECT 'an.murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an_murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an%murugappan@gmail.com'  mail FROM Dual
    )
    SELECT * FROM mail_ids
    WHERE mail LIKE '__$_%' ESCAPE '$';

mail
----------------------------------   
an_murugappan@gmail.com

   

WITH mail_ids AS
   (
     SELECT 'an.murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an_murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an%murugappan@gmail.com'  mail FROM Dual
    )
    SELECT * FROM mail_ids
    WHERE mail LIKE '__/%%' ESCAPE '/';
   
mail
----------------------------------   
an%murugappan@gmail.com

   
   
Escape ampersand (&) characters in SQL*Plus

SQL> select '&a' FROM dual;

'23'
----
23

SQL> SET ESCAPE '\'
SQL> select '\&a' FROM dual;

'&A'
----
&a

SQL> SET SCAN OFF;
SQL> select '&a' FROM dual;

'&A'
----
&a

SQL> SET SCAN ON;
SQL> select '&a' FROM dual;

'45'
----
45