比如: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']‘
———————-