Difference between single quote and double quote in Oracle SQL

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;

See the example below,

SELECT ename AS "Employee Name",  --Identifier
       empno AS EmpID,            --Identifier
       Job AS "Designation"       --Identifier
  FROM Emp
 WHERE job = 'MANAGER'            --Text Literal
   AND HireDate >= '01-JAN-1980'; --Date Literal

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 are used to enclose identifiers like table 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. Watch below 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.

CREATE TABLE Test_quotes(
  t_id        NUMBER,
  tname       VARCHAR2(100),
  tran date   DATE
);
--error: 

CREATE TABLE Test_quotes(
  t_id        NUMBER,
  'tname'     VARCHAR2(100),
  'tran date' DATE
);
--error

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

SELECT * FROM Test_quotes;

SELECT t_id, tname FROM Test_quotes; --error

SELECT t_id, "Tname" FROM Test_quotes; --error

SELECT t_id, "tname" FROM Test_quotes;

SELECT t_id, "tname", "tran date" FROM Test_quotes; --error

SELECT t_id, "tname", "Tran Date" FROM Test_quotes;

NOTE

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 used explicitly.

Simple but considerable ones..

Quoted strings placed next to each other are concatenated to each other and becomes a single string. So, the following lines are equivalent:

'You are reading article in The Code Man'
'You are reading ''article in ''The Code Man'

A single quotation mark (‘) within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.

SELECT "CodeMan", "'CodeMan'", "''CodeMan''", "Code""Man";

SELECT 'CodeMan', '"CodeMan"', '""CodeMan""', 'Code''Man';

SELECT "You've found this tutorial to be helpful"

SELECT 'They responded, "We found this tutorial helpful"'

SELECT 'They\'ve responded, "We found this tutorial helpful"'

SELECT 'They\'ve responded, "We found this tutorial helpful"' AS 'Response

Leave a Reply

Please Login to comment
  Subscribe  
Notify of
Close Menu
error: Content is protected !!