If we've spent any time writing SQL or PL/SQL in Oracle, we've run into the headache of single quotes inside text strings. Names like D'Souza, phrases like it's, or dynamic SQL with layers of nested strings all force us into the same clunky workaround: doubling up quotes ('Rahul''s book'). It works, but it's easy to miscount, hard to read, and a common source of syntax errors.
Oracle has a much cleaner answer: the Alternative Quoting Mechanism, better known as the q operator. Introduced in Oracle 10g, it lets us sidestep quote-escaping entirely by defining our own string delimiters.
Let's look at how to use it to keep SQL and PL/SQL clean and readable.
How It Works
The syntax is simple:
q'delimiter your_string delimiter'
We prefix the string with q, open with a single quote, choose a delimiter character, write the string as-is (apostrophes included), and close with the same delimiter followed by a single quote.
Oracle gives us two ways to pick a delimiter:
1. Paired bracket delimiters — [ ], { }, ( ), < >
We use an opening bracket, and Oracle automatically expects the matching closing bracket to end the string. This is usually the safest and most readable option.
2. Single-character delimiters — almost anything else
Characters like !, #, |, or ^ work fine too, as long as that character doesn't appear in the string.
Using It in Standard SQL
Since the q operator is a native SQL feature, we can use it anywhere we'd normally write a string literal.
In an INSERT statement:
INSERT INTO books (title, author) VALUES (q'{Chetan's Guide to IIT}', 'Chetan Bhagat');
In a WHERE clause:
WHERE last_name = q'<D'Souza>';
In a simple SELECT:
SELECT q'[It's a beautiful day in Bengaluru!]' AS greeting
FROM dual;No doubled-up quotes anywhere — just the string, exactly as written.
Using It in PL/SQL
The operator works identically inside PL/SQL blocks, which is especially handy when we're assembling dynamic SQL or working with variables that hold quoted text.
DECLARE
v_name VARCHAR2(30) := q'!Khushee's laptop!';
BEGIN
DBMS_OUTPUT.PUT_LINE('The first item is: ' || v_name);
v_name := q'[D'Silva's car]';
DBMS_OUTPUT.PUT_LINE('The second item is: ' || v_name);
END;
/Output:
The first item is: Khushee's laptop
The second item is: D'Silva's carNotice the two different delimiter styles in action: ! as a single-character delimiter in the first assignment, and [ ] as a paired bracket delimiter in the second. Both produce clean output with the apostrophe intact.
Why Make the Switch
- Instant readability — our code is easier to scan, review, and maintain, especially in strings with multiple apostrophes.
- Fewer bugs — no more counting single quotes to figure out where a string starts and ends.
- A dynamic SQL lifesaver — invaluable when building complex dynamic SQL strings where quotes would otherwise nest several layers deep.
Next time we catch ourselves typing '''' just to escape a string, let's stop, reach for the q operator instead and let a custom delimiter do the work for us.
