Alternative Quote (q) Operator

Description:

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { },

 ( ), or < >.

Examples:

 

SELECT department_name || q'[ Department's Manager Id: ]'

       || manager_id

      FROM departments;

The string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

SELECT department_name || q'55 Department's Manager Id: 55'

       || manager_id

      FROM departments;

And we can do it like this:

SELECT department_name || 'Department''s Manager Id:'

       || manager_id

      FROM departments;