The point of the Oracle escape character

Source: Internet
Author: User

In Oracle , some characters represent a special meaning, such as a & symbol, that represents a variable substitution, and% represents a wildcard character during a query

Here are some word relabeled that have special meanings in ORACLE to test:

Symbol

Function

Type

%

represent 0 to any character in a WHERE clause

Wildcard characters

_

The exact unknown character is represented in the WHERE clause

Wildcard characters

?

The exact position character is represented in the WHERE clause

Wildcard characters

#

The exact Arabic numerals in the WHERE clause

Wildcard characters

[A-d]

Represents a range of characters

Wildcard characters

Represents text, characters, and dates

Delimiter

"

Enclose column aliases that contain specific characters or spaces

Delimiter

&

Used to indicate a variable

Special symbols

||

String connection

Special symbols

*

represent 0 or more columns in a SELECT statement

Special symbols

/

Terminating SQL statements

Special symbols

Comments

Annotation characters

( Note: tested, above 3,4,5 Pass, not reliable, into trusting )

Here is a list of some commonly used special symbols, where we insert(directly write those can insert successful statements), the Statement installation table appears in the order of occurrence:

Sql> CREATE TABLE Test (name VARCHAR2 (20));

sql> INSERT into test values (' ABC3 ');

sql> INSERT into test values (' abc_123 ');

sql> INSERT into test values (' abc?123 ');

sql> INSERT into test values (' abc#123 ');

sql> INSERT into test values (' abc[a-d]123 ');

sql> INSERT into test values (' ABC ' 123 ');

sql> INSERT into test values (' ABC ' 123 ');

sql> INSERT into test values (' abc ' | | chr (38) | | ' 123 ');

sql> INSERT into test values (' abc| | 123 ');

sql> INSERT into test values (' abc*123 ');

sql> INSERT into test values (' abc/123 ');

sql> INSERT into test values (' abc123 ');

Sql> select * from test;

NAME

--------------------

Abc3

Abc_123

Abc?123

Abc#123

Abc[a-d]123

ABC ' 123

ABC "123

Abc&123

abc| | 123

Abc*123

Abc/123

abc123

You can see that in the insert operation above, in addition to the single quotation mark (') in the string that requires two repetitions, there is a special case is the ampersand (&), here is the ASCII Transcoding Way to do, about ASCII code can be queried in this way:

Sql> Select ASCII (' & ') from dual;

ASCII (' & ')

----------

38

Now that we've got this & out, we'll talk about it alone,

The way above applies to SQL scripts, a lot of SQL has & symbols, can be implemented in transcoding, if you are in sql*plus, you can also do this:

sql> INSERT into test values (' abc&test123 ');

Enter value for test123: &123

Old 1:insert into test values (' abc&test123 ')

New 1:insert into test values (' abc&123 ')

It's possible to do so, but it's against our intentions.

Another (using escape characters):

Sql> Show Escape

Escape OFF

Sql> set Escape on

sql> INSERT into test values (' abc\&123 ');

1 row created.

or (Turn the define function off directly):

Sql> Show define

Define "&" (Hex 26)

Sql> set Define Off

Sql> Show define

Define OFF

sql> INSERT into test values (' abc&123 ');

1 row created.

Now the data in the table is as follows:

Sql> select * from test;

NAME

--------------------

Abc3

Abc_123

Abc?123

Abc[a-d]123

ABC ' 123

ABC "123

Abc&123

abc| | 123

Abc*123

Abc/123

abc123

Abc#123

Abc&123

Abc&123

Abc&123

Abc3

Next, Select those things:

Since the first few characters are in effect in the WHERE clause, the preceding few are tested with a where clause (as in the above, the sentence is written directly to the test success):

Sql> SELECT * FROM test where name like ' abc\3 ' escape ' \ ';

NAME

--------------------

Abc3

Abc3

As you can see here, we specify the escape escape character ID, and of course the forward slash here can be other characters, for example:

Sql> SELECT * FROM test where name like ' abc=3 ' escape ' = ';

NAME

--------------------

Abc3

Abc3

OK, the reason is clear, the other characters are not listed, here only two more special examples, are single quotes and and symbols

Single quotes:

Here you can convert using ligatures two ways:

Sql> SELECT * FROM test where name like ' abc ' 123 ';

NAME

--------------------

ABC ' 123

However, when you try to escape it, you will find that the error (quoted string as the normal end, oh, well, it seems that the single quotation marks need special treatment):

Sql> SELECT * FROM test where name like ' abc\ ' 123 ' escape ' \ ';

ERROR:

Ora-01756:quoted string not properly terminated

&and characters:

Sql> SELECT * FROM test where name like ' abc\&123 ' escape ' \ ';

Enter value for 123: &

As you can see, if this escaped way is used to &, it doesn't work, but what if we close the Define ?

Sql> Show define

Define "&" (Hex 26)

Sql> set Define Off

Sql> Show define

Define OFF

Sql> SELECT * FROM test where name like ' abc\&123 ' escape ' \ ';

SELECT * FROM test where name like ' abc\&123 ' escape ' \ '

*

ERROR at line 1:

Ora-01424:missing or illegal character following the escape character

Can see, here will be error, this hint is said, followed by the escape character after the mistake or missing characters, it seems that can not oh, in fact, after we closed define , it can be written:

Sql> SELECT * FROM test where name is like ' abc&123 ';

NAME

--------------------

Abc&123

Abc&123

Abc&123

Abc&123

If you open escape, the default escape character is '\' and can be written as follows:

Sql> set Escape on

Sql> Show Escape

Escape "\" (Hex 5c)

Sql> SELECT * FROM test where name is like ' abc\&123 ';

NAME

--------------------

Abc&123

Abc&123

Abc&123

Abc&123

Of course, there is another way:

Sql> SELECT * FROM test where name is like ' abc ' | | Chr (38) | | ' 123 ';

NAME

--------------------

Abc&123

Abc&123

Abc&123

Abc&123

Well, almost so much, the rest of the Welcome supplement!

Note: Test platform:

OS Version:Redhat Enterprise Linux 5 Update 4 x86

DB Version:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

The point of the Oracle escape character

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.