Shulou(Shulou.com)05/31 Report--

This article is about how to use q-quote in Oracle 10g SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In Oracle, if the literal amount of a string contains single quotes, it must be escaped, and the escape character is a single quotation mark, so add the outer layer of the literal amount of the string, so it is bound to write a lot of single quotation marks, which is not only less readable, but also error-prone. Of course, using chr (39) can also replace quotation marks, which may be better and readable than writing multiple single quotes, but after all, this is not very common. SO, in 10g, Oracle introduces the use of q-quote.

The use of single quotation marks within the literals of regular characters:

Dingjun123@ORADB > select''str from dual

ERROR:

ORA-01756: the string in quotation marks does not end correctly

To use escape, two single quotes indicate a single quote

Dingjun123@ORADB > select''str from dual

S

-

'

Dingjun123@ORADB > select''a few 'str from dual'

STR

-

'a'

In addition, you can use CHR (39) as quotation marks to piece together, which is relatively simple.

Dingjun123@ORADB > SELECT 'It' | | chr (39) | |' a cat' str FROM dual

STR

-

It' a cat

Although using chr (39), it still looks troublesome, so 10g introduces the q-quote feature. It is allowed to start with Q or Q according to the specified rule (in the case of national character literals, N or n is placed before Q or Q), and then 'define the original literal quantity of the symbol'. The rule is simple:

1) the q-quote delimiter can be any single-byte or multi-byte character except TAB, space, carriage return.

2) except that the opening delimiter of a single byte is [, {,) other start delimiters and end delimiters must be the same.

Such as:

-- 4 special single-byte start delimiters are [, {,)

Dingjun123@ORADB > SELECT Q'[a'bc] 'str FROM dual

STR

-

A'bc

Dingjun123@ORADB > SELECT q' {a'bc} 'str FROM dual

STR

-

A'bc

Dingjun123@ORADB > SELECT QQ 'str FROM dual

STR

-

A'bc

Dingjun123@ORADB > SELECT Q'(a'bc) 'str FROM dual

STR

-

A'bc

Because [starting to define the payment is multi-byte, it must be consistent, especially for four single-byte delimiters.

Dingjun123@ORADB > SELECT Q' [a'bc ['FROM dual]

ERROR:

ORA-01756: the string in quotation marks does not end correctly

Dingjun123@ORADB > SELECT Q' [a'bc ['FROM dual]

Q'[

-

A'bc

-- because] is not the beginning of four special delimiters

Dingjun123@ORADB > SELECT q'] a'bc ['FROM dual

ERROR:

ORA-01756: the string in quotation marks does not end correctly

Dingjun123@ORADB > SELECT q'] a'bc] 'FROM dual

Q'] A

-

A'bc

The requirements of the other four non-special start delimiters are consistent with the beginning and the end.

-- an is the delimiter

Dingjun123@ORADB > SELECT qintabouts cda' str FROM dual; STR

-

B'cd

-- An is the delimiter, but ends with a, inconsistent

Dingjun123@ORADB > SELECT qunction Abbott cda' str FROM dual

ERROR:

ORA-01756: the string in quotation marks does not end correctly

Dingjun123@ORADB > SELECT qure Abbott cdA' str FROM dual

STR

-

B'cd

-- the + sign is the delimiter

Dingjun123@ORADB > SELECT QQ / cdfdl / str FROM dual

STR

-

B'cd

-- single quotation marks are delimiters

Dingjun123@ORADB > select qabilistic abutting canals' from dual

QQ

-

Ab'c

-- National character literals

Dingjun123@ORADB > SELECT nq'' str from dual

STR

-

A'b'c

Dingjun123@ORADB > SELECT Nq'' str from dual

STR

-

A'b'c

Dingjun123@ORADB > SELECT NQ'' str from dual

STR

-

A'b'c

Dingjun123@ORADB > SELECT nQ'' str from dual

STR

-

A'b'c

In general, 4 special characters are used as delimiters, so that the pre-and post-delimiters contain the original string, which makes it more readable.

Thank you for reading! This is the end of the article on "how to use q-quote in Oracle 10g SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.