Difference between single quote and double quote in Oracle SQL

In this article, you will understand when and where to use single and double quotes in Oracle.

To begin with, let’s first understand what is a Literal and what are Identifiers in Oracle databases. You can go through Chapter – Literals in Oracle to understand the types of Literals you can use in Oracle database.

Below diagram shows and explains in short what is Literal vs Identifier.

Single quotes in Oracle

Single-quotes are used to enclose string literals of zero or more characters in Oracle. A string literal can hold up to 32,767 characters. If no characters are placed within single quotes then that is called a null string (”) and has length as zero. Single-quotes are also used to declare DATE literals in Oracle.

Double quotes in Oracle

Double-quotes are used to enclose identifiers like table name/table alias or column name/column alias. They are rarely used when the name doesn’t need to conform to Oracle Database Object Naming Rules. Using double-quotes to represent identifiers is not recommended.

What Oracle documentation says

Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.

Single QuoteDouble Quote
Used to declare string and date literals.
e.g. SELECT 'This is your string' test_str FROM DUAL;
SELECT '31-DEC-2019' test_date FROM DUAL;
Used to declare identifiers like table name or column name.
e.g.
SELECT ename AS "Employee Name" FROM Emp;

Explore the power of double quotes

Non-quoted identifiers cannot be Oracle SQL reserved words. Also, non-quoted identifiers can not have white spaces between words. But these limitations do not hold good with quoted identifiers. Quoted identifiers can be reserved words, can have spaces between words, although these are not recommended.

--Error! Object names can't be reserved words

CREATE TABLE Select
(
  t_id         NUMBER,
  tname        VARCHAR2(100),
  transdate    DATE
);

CREATE TABLE Select
             *
ERROR at line 1:
ORA-00903: invalid table name
--Reserved words can be object names through quoted strings

CREATE TABLE "Select"
(
  t_id         NUMBER,
  tname        VARCHAR2(100),
  transdate    DATE
);

Table created.
--Error! Identifier names can not have white spaces between words

CREATE TABLE Test
(
  t_id          NUMBER,
  tname         VARCHAR2(100),
  trans date    DATE
);

  trans date	DATE
                *
ERROR at line 5:
ORA-00907: missing right parenthesis
--Quoted identifiers can have spaces between words

CREATE TABLE Test
(
  t_id            NUMBER,
  tname           VARCHAR2(100),
  "trans date"    DATE
);

Table created.

Why Oracle doesn’t recommend quoted identifiers for table names or column names?

Quoted identifiers are great option to consider for reporting (data representation) where meaningful column header gives a lot of information of what data is projected. But providing table names or column names with double quotes often makes data access complicated.

Let’s understand the complications of quoted identifiers through some examples.

--Creating table with double quotes

CREATE TABLE "Test"
(
  t_id         NUMBER,
  "Tname"      VARCHAR2(100),
  "Tran Date"  DATE
);

Table created.


--Error! Oracle raises error when no double quotes used to access quoted identifiers

SELECT * FROM test;
SELECT * FROM test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Double quotes preserves the case of the string. Oracle metadata stores object names in upper case though, but it is an exception in case double quotes are used explicitly.

Oracle raises error when quoted identifier case does not match between the column at table creation and case specified during selection. Quoted identifiers preserves the case. Mention the proper case when accessing quoted identifiers.

--Error! Oracle raises error when double quotes used, but case does not match. Double quotes preserves the case of the string.

SELECT * FROM "test";
SELECT * FROM "test"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


--Error! Oracle metadata stores object names in upper case though, but is an exception in case double quotes are used explicitly.

SELECT * FROM "TEST";
SELECT * FROM "TEST"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


--Using proper character case within double quotes
SELECT * FROM "Test";

no rows selected

To access individual table columns which are declared as quoted identifiers, you must enclose the column name within double quotes with proper case as quoted identifiers preserves the case of the string. Oracle metadata stores object names in upper case though, but it is an exception in case double quotes are used explicitly.

You can specify double quotes for non-quoted identifiers too, but must specify names with upper case as Oracle stores metadata in upper case.

--Use double quotes with proper case to access columns

SELECT "T_ID", "Tname", "Tran Date" FROM "Test";

no rows selected

Using single quotes to define Literals in Oracle

--Error: Table/column names cannot be defined using single quote

CREATE TABLE Test1
(
  t_id        NUMBER,
  'tname'     VARCHAR2(100),
  'tran date' DATE
);

  'tname'     VARCHAR2(100),
  *
ERROR at line 3:
ORA-00904: : invalid identifier

They are required during DML operation or during data retrieval.

CREATE TABLE Test1
(
  t_id        NUMBER,
  tname       VARCHAR2(100),
  tran_date   DATE
);


--Use single quotes to declare string and date literals

INSERT INTO Test1 VALUES(100, 'TEST_100', '20-NOV-19');
INSERT INTO Test1 VALUES(200, 'TEST_200', SYSDATE);
INSERT INTO Test1(t_id, tname) VALUES(300, 'TEST_300');
COMMIT;


--Fetching rows from table

SELECT * FROM Test1
WHERE tname = 'TEST_300' AND tran_date IS NULL;

      T_ID TNAME		        TRAN_DATE
---------- -------------------- ---------
       300 TEST_300             

Escape Single Quotes in Oracle SQL

What is Escaping? Let us understand this with below example.

Q : Write a query to get the following output?

The'CodeMan, is helpful in "learning" and "knowing".

Ans: Use two single quotes for every one quote to display.

SELECT
  'The''CodeMan, is helpful in "learning" and "knowing".' AS "Output Text"
  FROM DUAL;

Output Text
----------------------------------------------------
The'CodeMan, is helpful in "learning" and "knowing".

What is escaping with single quotes and why is it required?

The single quote character in Oracle indicates the start and end position of your string. But what if you want to have a single quote as part of your string? This would cause an issue, because there would be three single quotes. Oracle doesn’t know where your string should end.

So, to allow values within single quotes (and some other special characters) to be used within a string, 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”.

How to escape single quotes?

Method 1:

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.

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

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


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

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

Method 2:

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.

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

Your String
-----------------------------------
You are reading article in DcodeMan


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

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

Method 3:

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.

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

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


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

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

This approach though looks good, but is not used much in Oracle database world.

Look at following examples

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


--2
SELECT 'CodeMan' Col1 FROM DUAL;

COL1
-------
CodeMan


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

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


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

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


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

COL1	     COL2
------------ --------------
The'Code'Man The"Code""Man"
--6
SELECT 'Have You found this tutorial helpful?' "Feedback Request" 
  FROM DUAL;

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


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

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


--8
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.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments