Stop Escaping Quotes in Oracle SQL: Meet the q Operator

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 O'Connor, phrases like it's, or dynamic SQL with layers of nested strings all force us into the same clunky workaround: doubling up quotes ('John''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'{The Hitchhiker's Guide to the Galaxy}', 'Douglas Adams');

In a WHERE clause:

SELECT employee_id, first_name, last_name
FROM employees
WHERE last_name = q'<O'Neil>';

In a simple SELECT:

SELECT q'[It's a beautiful day!]' 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'!John's book!';
BEGIN
  DBMS_OUTPUT.PUT_LINE('The first name is: ' || v_name);

  v_name := q'[Smith's book]';
  DBMS_OUTPUT.PUT_LINE('The second name is: ' || v_name);
END;
/

Output:

The first name is: John's book
The second name is: Smith's book

Notice 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.