Summary of Solutions for Inserting Special Characters: And 'in Oracle

Today, when importing a batch of data to Oracle, I encountered such a problem: Toad prompted to assign a value to a custom variable AMP. At first, I wondered, the data is a series of Insert statements, how can there be custom variables? Later, I searched for the keyword AMP under 1, and found that it was because there was a field in the inserted data with the following contents:

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

Oracle puts the parameter connector of URL here & amp; As a custom variable, I was asked to assign a value to the variable AMP. After testing, the following three methods are summarized:

Method 1: Add Set define off before the SQL statement to be inserted; Batch execution with the original SQL statement 1

We execute SQL under SQL*PLUS > show all command, you can find one parameter: define " & "(hex 26) with the following code

......
concat "." (hex 2e)
copycommit 0
copytypecheck ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT OFF
echo OFF
......

This is the setting used to identify custom variables in Oracle. Now we turn it off under SQL*PLUS:

SQL> Set define OFF;

Then execute the import script again, OK! Problem solved.

Note: If it is executed in TOAD, it is recommended to add the previous sentence to close define at line 1 of every script to be imported, otherwise, when you import the second script with special characters, there will be an error again.
If it is executed in SQL*PLUS, you only need to set define OFF once, and then you can import it continuously. Until you reset define ON.

Insert a statement:

SQL> insert into AA (O,resvalue) values ('AA', 'S'||chr(38)||'P');

Method 2: In the SQL statement, add ' & 'Replace with chr (38) because chr (38) is' & ASCII code of '

SQL> Select 'Tom' || chr(38) || 'Jerry' from dual;

Method 3: Split the original string

SQL> Select 'Tom' || '&' || 'Jerry' from dual;

As we can see, Method 1 is the simplest and most efficient. Method 2 has a slightly poor performance because it has a procedure to call the function. Method 3 needs to connect strings twice, which is the worst efficiency!

So how do you insert single quotation marks if the contents of a field contain them? For example: It 's fine. In this case, there are also three methods:

Method 1: Use escape characters

SQL > Select 'test' || '''' from dual;

Note: What do you mean by the four single quotation marks here? First, the first and last are string connectors in Oracle, and there is no objection to this. So what do the second'and the third 'mean? The second'is an escape character
The third'is what we really have

Method 2: Use escape characters again, but in a different way

SQL > Select 'test ''' from dual;

Note: The second and third'here is the escape character and the real content mentioned in method 1 above

Method 3: Replace'with chr (39) in SQL because chr (39) is the ASCII code for '

SQL > Select 'It' || chr(39) || 'fine' from dual;