|Single Quote||Double Quote|
|Used to declare string and date literals.|
|Used to declare identifiers like table name or column name.|
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;
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