Variable substitution in Oracle's SQL statements

I. Description of the problem

The following sql:

INSERT  into TMP (val)VALUES('a&b');

View Code

The following prompts will appear during execution:

After clicking "OK", we look at the data in the table:

The string after B is not inserted.

Two. Reason Analysis-substitution variables

The default "&" in Sql*plus represents an alternative variable, and when writing &, the database requires it to be replaced with another value.

For example, you can define:

The value in this insert database is Ahellob.

Three. Workaround

Workaround 1: Turn off variable substitution

As shown below:

SET OFF ; INSERT  into TMP (val)VALUES('a&b'); COMMIT;

View Code

The value that is inserted in this way is a&b

Solution 2: Use the ASCII code of the & symbol Chr (38) to handle

string concatenation , as shown below:

INSERT  into TMP (val)VALUES('a'| | CHR| |  ' b ' ); COMMIT;

View Code

The string is translated as follows:

INSERT  into TMP (val)VALUES(Translate ('aiiib','III' , CHR)); COMMIT;

View Code

The string is replaced as follows:

INSERT  into TMP (val)VALUES(REPLACE('aiiib','III  ", CHR)); COMMIT;

View Code

Attached: ASCII code table

Baidu Encyclopedia: http://baike.baidu.com/view/15482.htm?fromid=99077

Variable substitution in Oracle's SQL statements