The point of the Oracle escape character

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