How To Escape Special Characters in Writing SQLs!

Oracle DB reserved below characters with specific meaning and purpose.

1. _ (underscore) wildcard character is used to match exactly one character
2. % (percentage) is used to match zero or more occurrences of any character
3. ‘ (apostrophe, quote) is used to mark the value supplied

     If you want to insert the data with any of these characters or when performing string search with LIKE keyword, they are not interpreted literally and cause errors. One should escape them if these need to be interpreted as part of string value instead of a pre-defined meaning.

Let’s look at how each of these characters can be escaped.

Escape wildcard characters:

The LIKE keyword allows for string searches. As mentioned earlier, _ is used to match exactly one character.

1 2	SQL> SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/'; SQL> SELECT name FROM emp WHERE id LIKE '%\%%' ESCAPE '\';

Escape quotes('):

Quotes can be escaped in two ways. Let’s look at each.

•      Use another quote to escape one quote. For example,

1 2 3 4 5

SQL> SELECT 'VENKI''S' AS WHOS FROM DUAL; WHOS ------- VENKI’S

Another example is... 

1 2 3 4 5

SQL> SELECT 'VENKI''S LAPTOP: ''''VAIO''''' AS WHATS FROM DUAL; WHATS --------------------- VENKI’S LAPTOP: ''VAIO''

•        Use q expression. For example

1 2	SQL> SELECT q'[VENKI'S]' AS QUOTE_S FROM DUAL; SQL> SELECT q'[VENKI'S LAPTOP: ''VAIO'']' AS QUOTE_S FROM DUAL;

Escape Ampersand (&):

This can be escaped in 3 ways. Let’s look at each.

•        Use DEFINE. For example,

1 2 3

SQL> CREATE TABLE temp (name VARCHAR2(30)); SQL> SET DEFINE ~ SQL> INSERT INTO temp VALUES('Tom & Jerry');

Instead of ~, you can use ON or OFF to turn it on or off.

•        Use ESCAPE. For example,

1 2	SQL> SET ESCAPE '\' SQL> SELECT 'Tom \& Jerry' FROM DUAL;

You can define any other character also as escape character.

•        Use SCAN. For example,

1 2	SQL> SET SCAN OFF; SQL> SELECT 'Tom & Jerry' FROM DUAL;

But, this has been made obsolete. Please see for some more obsolete commands in Oracle DB https://docs.oracle.com/cd/B19306_01/server.102/b14357/apc.htm

•        Another best way is to use to concatenation to escape the &.

1	SQL> SELECT 'Tom ' || '&' || ' Jerry' FROM DUAL;

Happy Learning… J