Oracle Text Literals (Q quote_delimiter )
来源:互联网 发布:钟振振厉害吗 知乎 编辑:程序博客网 时间:2024/04/30 12:16
在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.
0 0
- Oracle Text Literals (Q quote_delimiter )
- Literals
- Basic Elements of Oracle SQL 之 Literals
- Oracle Q-quote delimiter
- Oracle Q-quote delimiter
- oracle text
- oracle text
- String Literals
- ABAP Literals
- object literals
- Compound Literals
- Oracle PL/SQL Q&A
- Oracle q' 简化单引号转义
- Oracle之q操作符
- Oracle Text 全文检索
- Oracle Text(全文索引)
- ORACLE TEXT是什么
- Oracle Text 组件 说明
- Android 记录和恢复ListView滚动的位置的两种方法
- C++11标准之右值引用(ravalue reference)
- leetcode链表相关题
- java在dos中打war包
- poi jar
- Oracle Text Literals (Q quote_delimiter )
- Android ListView组件中android:drawSelectorOnTop含义
- merkle tree
- Catalan数
- 走进C++程序世界----STL标准库
- 《程序员,你伤不起》读书笔记 (六) :IT软件创业之 -- 曾经热血沸腾软件创业、技术带人犯下的幼稚错误总结
- 在VC中读写ini配置文件
- 关于java协程和kilim的一些概念
- HDU 1237简单计算器