Single quotes are used in databases to represent string and date values. The single quotes indicates the start and end position of your string.

SQL> SELECT 'This is your string' str FROM DUAL;

STR
-------------------
This is your string

But what if you want to have a single quote as part of your string? This means how do you represent something like this ‘This is your’s’?

Insert Single Quote to table column

Sometimes you might want to insert a single quote as part of your data or you want the data output with single quotes. You can not directly add another single quote in the string (for example ‘This is your’s’) as there will be three single quotes and Oracle doesn’t know where your string should end.

So, to allow single quotes and some other special characters within string values, you need to “escape” them. Escaping a character is where you say to the Oracle database, “Hey, this character here is part of my string, don’t treat it as a special (string start and ending) character like you normally would”.

Now let’s cover the different approaches available to escape single quotes or special characters in a string.

Approaches to escape single quotes

Method 1: 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 every one quote to display. This is the most common methodology used in Oracle to escape single quotes in data values.

SQL> SELECT 'You''re reading article in DcodeMan' AS "Your String"
  FROM DUAL;

Your String
----------------------------------
You're reading article in DcodeMan


SQL> SELECT 'You''re reading article in ''DcodeMan''' AS "Your String"
  FROM DUAL;

Your String
------------------------------------
You're reading article in 'DcodeMan'

Method 2: Use CHR() function with string concatenation

Another method is to use the Oracle CHR() function. The CHR() function returns a character from the specified ASCII code. CHR(39) returns a single quote, which you can concatenate to your string.

This method is useful when you want to use dynamic SQL.

SQL> SELECT 'You'||CHR(39)||'re reading article in DcodeMan' AS "Your String"
  FROM DUAL;

Your String
-----------------------------------
You're reading article in DcodeMan


SQL> SELECT 'You'||CHR(39)||'re reading article in '||CHR(39)||'DcodeMan'||CHR(39) AS "Your String"
  FROM DUAL;

Your String
-------------------------------------
You're reading article in 'DcodeMan'

Method 3: Use Literal Quoting

Another Oracle SQL escape single quote method you can use is “Literal quoting”. How you do is, put the letter “q” in front, place the string exactly the way you want it to be displayed within square brackets and enclose square brackets with single quotes. This approach is not used much in Oracle database world.

SQL> SELECT q'[You're reading article in DcodeMan]' AS "Your String"
  FROM DUAL;

Your String
----------------------------------
You're reading article in DcodeMan


SQL> SELECT q'[You're reading article in 'DcodeMan']' AS "Your String"
  FROM DUAL;

Your String
------------------------------------
You're reading article in 'DcodeMan'

Look at some more examples to escape special characters in a string

--1
SQL> SELECT "CodeMan" Col1 FROM DUAL;
SELECT "CodeMan" Col1 FROM DUAL
       *
ERROR at line 1:
ORA-00904: "CodeMan": invalid identifier


--2
SQL> SELECT 'CodeMan' Col1 FROM DUAL;

COL1
-------
CodeMan


--3: Double quotes enclosed within single quotes
SQL> SELECT 'Code"Man' Col1 FROM DUAL;

COL1
--------
Code"Man


--4: Two Single quotation marks enclosed within single quotes
SQL> SELECT 'Code''Man' Col1 FROM DUAL;

COL1
--------
Code'Man


--5
SQL> SELECT 'The''Code''Man' Col1, 'The"Code""Man"' Col2 
  FROM DUAL;

COL1	     COL2
------------ --------------
The'Code'Man The"Code""Man"


--6
SQL> SELECT 'Have You found this tutorial helpful?' "Feedback Request" 
  FROM DUAL;

Feedback Request
-------------------------------------
Have You found this tutorial helpful?


--7
SQL> SELECT 'He responded, "I found this tutorial helpful"' AS "Individual Feedback"
  FROM DUAL;

Individual Feedback
---------------------------------------------
He responded, "I found this tutorial helpful"


--8
SQL> SELECT q'[They've responded, "We found this tutorial helpful"]' AS "Team Feedback Received"
  FROM DUAL;
Team Feedback Received
---------------------------------------------------
They've responded, "We found this tutorial helpful"

Hope, this article helped you learn something interesting and examples made you fascinated. Please comment and provide your feedback if you like it.