在SQL或PL/SQL中,是表示一个字符串时,通常要用单引号('')括起来。
比如:insert或是select的时候。
insert into test values('test');
select 'test' from dual;
但如果字符串本身包含'或者"这样的字符,那么实现起来有点麻烦。
比如: Smith's bag
--直接用单引号括起来,会报错,因为不配对
SQL> select 'Smith's bag' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
--可以用一个'符号来转义
SQL> select 'Smith''s bag' sb from dual;
SB
----------------------
Smith's bag
--双引号,则不用转义,它还是代表他本来的意义
SQL> select '"Smith"s bag' sb from dual;
SB
------------------------
"Smith"s bag
--要显示'Smith',需要写成'''Smith'''
SQL> select '''Smith''' sb from dual;
SB
-----------
'Smith'
SQL> select '''Smith''s bag''' sb from dual;
SB
--------------------------
'Smith's bag'
在Oracle中,single-quote(')是一个表示字符串的关键字。所以在字符串中用两个''表示一个实际的单引号字符。
其实Oracle提供了一个Q-quote的表达式,用来简化SQL或PLSQL中字符串的表示。
SQL> select q'[Smith's bag]' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'/Smith's bag/' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'#Smith's bag#' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'<Smith's bag>' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'\Smith's bag\' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'|Smith's bag|' sb from dual;
SB
----------------------
Smith's bag
SB
----------------------
Smith's bag
SQL> select q'/Smith's bag/' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'#Smith's bag#' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'<Smith's bag>' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'\Smith's bag\' sb from dual;
SB
----------------------
Smith's bag
SQL> select q'|Smith's bag|' sb from dual;
SB
----------------------
Smith's bag
语法还是比较简单的,必须将要表示的字符串用一对特殊字符括起来,这对字符必须一致。
字符对不一致,就会报错,比如下面的例子:
SQL> select q'[Smith's bag|' sb from dual;
ERROR:
ORA-01756: quoted string not properly terminated
ERROR:
ORA-01756: quoted string not properly terminated
联机文档描述原文:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF00218
1) Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string.
2) The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing quote_delimiter.
3) c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.
4) quote_delimiter is any single- or multibyte character except space, tab, and return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark.
If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.