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

在SQL或者PLSQL中如果要表达一个带有特殊字符的字符串时，通常要用‘’包装起来。但如果字符串本身包含‘或者“这样的字符，那么实现起来有点繁琐。

比如：My Name is ‘Ma Yu Ping’

用SQL实现：

SQL> select ‘My Name Is ”Ma Yu Ping”’ names from dual;

NAMES 
———————————————- 
My Name Is ‘Ma Yu Ping’

SQL> select ‘My Name Is "Ma Yu Ping"’ names from dual;

NAMES 
———————————————- 
My Name Is "Ma Yu Ping"

在Oracle中，single-quote(‘)是一个表示字符串的关键字。所以在字符串中用两个”表示一个实际的单引号字符。所有才会有了上面第一条SQL的’My Name Is ”Ma Yu Ping”’ 。双引号“被识别为一个实际的的字符串，第二条sql中的双引号不用括引。其实Oracle提供了一个Q-quote的表达式，用来简化SQL或PLSQL中字符串的表示。

SQL> select q’[My Name Is "Ma Yu Ping"]‘ names from dual;

NAMES 
———————————————- 
My Name Is "Ma Yu Ping"

SQL> select q’[My Name Is 'Ma Yu Ping']‘ names from dual;

NAMES 
———————————————- 
My Name Is ‘Ma Yu Ping’

语法很简单，必须将要表示的字符串用一对特殊字符括起来，这对字符必须一致。

SQL> select q’[My Name Is 'Ma Yu Ping'|' names from dual; 
ERROR: 
ORA-01756: 引号内的字符串没有正确结束

SQL> select q'|My Name Is 'Ma Yu Ping'|' names from dual;

NAMES 
---------------------------------------------- 
My Name Is 'Ma Yu Ping'

常用简化的写法，比如一个where c='d'的产量表达式的表示方法。

SQL> select 'where c=''d''' from dual; －－老的写法

'WHEREC=''D''' 
---------------------- 
where c='d'

SQL> select 'where c='d'' from dual; －－原则的写法受’表达式的影响出错 
select 'where c='d'' from dual 
                  * 
第 1 行出现错误: 
ORA-00923: FROM keyword not found where expected

SQL> select q'[where c='d']‘ from dual; －－使用q-quote表达式的写法

Q’[WHEREC='D']‘ 
———————- 
where c=’d’

come from: