Oracle SQL Quote Operator

Sometimes we use single quotation mark in our sentence. This quotation mark delimiter is specified by quote operator. So quote(q) operator is used in SQL statements to show single quotation mark. Quote operator is not supported in 9i. This operator will support for 10g or higher.

 

For using the quote (q) operator, We can use single or multi byte delimiter or any of the following character pairs:

–> []
–> {}
–> ()
–> <>

 

Syntax of quote (q) operator:
=====================

q'[text to be quoted]’

 

Example:
======

First of all, I am writing a sentence in SQL statement with single quotation mark and with out quote (q) operator below:

SELECT 'I'm using quote operator in SQL statement'
FROM dual;

 

After executing the above query, I am getting below error from oracle database.

ERROR:
ORA-01756: quoted string not properly terminated

 

Now, I am writing same sentence again in SQL statement with quote (q) operator below:

SELECT q'[I'm using quote operator in SQL statement]' "Quote (q) Operator"
FROM dual;

Here, I used “Quote (q) Operator” for aliases. Above statement show the below result:

Quote (q) Operator
-----------------------------------------
I'm using quote operator in SQL statement

 

We can use any character as single quotation mark delimiter.

Example:
=======

SQL statement:

SELECT q'{Oracle's quote operator in SQL}' "Quote Operator"
FROM dual;

Answer:

Quote Operator
------------------------------
Oracle's quote operator in SQL

 

SQL statement:

SELECT q'XThis is my Blog. It's personal.X' "Quote Operator"
FROM dual;

Answer:

Quote Operator
-------------------------------
This is my Blog. It's personal.

 

SQL statement:

SELECT first_name||' '||q'<Employee's Manager id: >'|| manager_id "Q Operator"
FROM employees
WHERE department_id = 50;

Answer:

Q Operator
-----------------------------------
Douglas Employee's Manager id: 124
Matthew Employee's Manager id: 100
Adam Employee's Manager id: 100
Payam Employee's Manager id: 100
Shanta Employee's Manager id: 100
Kevin Employee's Manager id: 100
Julia Employee's Manager id: 120
Irene Employee's Manager id: 120
James Employee's Manager id: 120
Steven Employee's Manager id: 120

 

SQL statement:

SELECT last_name ||' '||q'+Department's ID : +'||department_id "Dept ID"
FROM employees
WHERE manager_id = 100;

Answer:

Dept ID
--------------------------------
Hartstein Department's ID : 20
Kochhar Department's ID : 90
De Haan Department's ID : 90
Raphaely Department's ID : 30
Weiss Department's ID : 50
Fripp Department's ID : 50
Kaufling Department's ID : 50
Vollman Department's ID : 50
Mourgos Department's ID : 50
Russell Department's ID : 80
Partners Department's ID : 80