字符串⽂字中包含引号
常常有⼈写SQL时不知道在字符串内的单引号怎么写，其实只要把⼀个单引号换成两个单引号表⽰就可以。
⽰例如下：
SELECT '''' AS c FROM DUAL
UNION ALL
SELECT 'GOO''D' AS c FROM DUAL;
另外，Oracle10g开始引⼊了q-quote特性，使⽤q-quote的写法就⽐较明确了：
SELECT q'[']' AS c FROM DUAL
UNION ALL
SELECT q'[GOO'D]' AS c FROM DUAL;
Oracle10g的SQL参考⼿册中相关描述如下：
Use the text literal notation to specify values whenever 'string' or appears in the syntax of expressions, conditions, SQL
functions, and SQL statements in other parts of this reference. This reference uses the terms text literal, character literal, and string interchangeably. Text, character, and string literals are always surrounded by single quotation marks. If the syntax uses the term char, you can specify either a text literal or another expression that resolves to character data — for example, the last_name column of ployees table. When char appears in the syntax, the single quotation marks are not used.
where N or n specifies the literal using the national character set (NCHAR or NVARCHAR2 data). By default, text entered using this notation is translated into the national character set by way of the database character set when used by the server. To avoid potential loss of data during the text literal conversion to the database character set, set the environment variable字符串常量既可以用双引号
ORA_NCHAR_LITERAL_REPLACE to TRUE. Doing so transparently replaces the n’ internally and preserves the text literal for SQL processing.
In the top branch of the syntax:
c is any member of the user's character set. A single quotation mark (') within the literal must be precede
d by an escape
character. To represent one single quotation mark within a literal, enter two single quotation marks.
' ' are two single quotation marks that begin and end text literals.
In the bottom branch of the syntax:
Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string.
The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing
quote_delimiter.
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.
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.
Text literals have properties of both the CHAR and VARCHAR2 datatypes:
Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
A text literal can have a maximum length of 4000 bytes.
Here are some valid text literals:
'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'
Here are some valid text literals using the alternative quoting mechanism:
q'!name LIKE '%DBMS_%%'!'
q'<'So,' she said, 'It's finished.'>'
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
nq'ï Ÿ1234 ï'
q'"name like '['"'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。