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