Oracle Inserts special characters "&" and Spaces

Today, when importing a batch of data to Oracle, I ran into a problem: Toad hints to assign a custom variable amp, and at first I was wondering if the data is a series of INSERT statements, how can I have a custom variable? Then I searched for the keyword amp discovery, originally because there is a field in the insert data that reads as follows:

http://xxx.com/3DX?uid=0676&sid=rt_060908

Oracle took the URL's parameter connector & as a custom variable, so I was asked to assign a value to the variable amp. After testing, the following three methods are summed up:

Method One: Add set define off before the SQL statement to be inserted, and batch execution with the original SQL statement

When we execute the sql> Show all command under Sql*plus, we can find a parameter: Define "&" (Hex 26), as shown in the code below

" . "  0 "&" 1 linenum Off INDENT offecho off ...

This is the setting used in Oracle to identify the custom variables, and now we turn them off under Sql*plus:

   >  Set define  OFF；

Then execute the import script again, ok! Problem is done.

Note: If you are executing in toad, it is recommended that the first line of the script to be imported, plus the previous one, be closed define, otherwise you will get an error when you import a second script that contains special characters.
If you are executing in sql*plus, you only need to set the define OFF once, and you can import it continuously later. Until you reset define on.

Insert a statement:

> INSERT into AA (O,resvalue) VALUES ('AA'S'| | chr || ' P ');

Method Two: Replace ' & ' with Chr (38) in the SQL statement, because CHR (38) is the ASCII code of ' & '

' Tom ' | | CHR'Jerry' from dual

Method Three: Split the original string

' Tom ' ' & ' ' Jerry '  from dual;

As we can see, the method is the simplest and most efficient. Method Two because there is a procedure to call the function, so the performance is slightly worse. Method three needs two times the connection string, the efficiency is the worst!

So what if the contents of the field contain single quotes? For example: It ' s fine. In this case there are also three ways:

Method One: Use the escape character

  ' Test ' " '"  from dual

Note: What does the "four single quotes" mean? First and last are the string connectors in Oracle, this is no objection. So what does the second ' and third ' mean? The second ' is an escape character
The third ' is our real content.

Method Two: The same is the use of escape characters, but the way is different

  " from dual;

Note: Here's the second, third ' is the escape character and the real content that we mentioned in method one above

Method Three: Replace ' in SQL ' with Chr (39) because Chr (39) is ' ASCII code

' It ' | | Chr (fine ' from dual;

Original address: http://www.jb51.net/article/52135.htm

Oracle Inserts special characters "&" and Spaces