Oracle q-quote (Quoting Character Literals)

By default you must quote character literals in single-quotes, as in 'Hello'. This technique can sometimes be inconvenient if the text itself contains single quotes. Oracle 10g allows you to define your own string delimiters to remove the need to double up any single quotes. Any character that is not present in the string can be used as the delimiter. The Q-quote mechanism, which enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter.

Q-quote delimiter

The Q-quote delimiter can be any single- or multibyte character except space, tab, and return. If the opening quote delimiter is a [, {, <, or ( character, then the closing quote delimiter must be the corresponding ], }, >, or ) character. In all other cases, the opening and closing delimiter must be the identical character.

For example, the literal q'#it's the "final" deadline#' uses the pound sign (#) as a quote delimiter for the string it's the "final" deadline.

Lets Play:

SQL> select q'(name LIKE '%DBMS_%%')' as Result from dual;

RESULT
--------------------
name LIKE '%DBMS_%%'

SQL> select q'<'Data,' he said, 'Make it so.'>' as Result from dual;

RESULT
------------------------------
'Data,' he said, 'Make it so.'

SQL> select q'<select q'"name like '['"' as result from dual;

RESULT
-------------
name like '['

SQL> select q'<select q'a1234a' as result from dual;

RESULT
-----
1234

SQL> SET SERVEROUTPUT ON
BEGIN
  -- Orginal syntax.
  DBMS_OUTPUT.put_line('This is Tim''s string!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Tim's string!#');
  DBMS_OUTPUT.put_line(q'[This is Tim's string!]');
END;
/

This is Tim's string!
This is Tim's string!
This is Tim's string!

PL/SQL procedure successfully completed.

For more information see:

Text Literals - Quoting Character Literals - Oracle q-quote