How to insert special characters:& and ' (Multiple solutions) in Oracle

Today, when importing a batch of data to Oracle, there was a problem: to give a custom variable amp a value, 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://mobile.three.com.hk/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. What do we do?

There are three methods:

Method One:

Add set define off before the SQL statement to be inserted; 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

......
Concat "." (Hex 2e)
Copycommit 0
Copytypecheck on
define "&" (Hex 26)
Describe DEPTH 1 linenum off INDENT off
Echo OFF
......

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

sql> 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.

Method Two:

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

sql> Select ' Tom ' | | Chr (38) | | ' Jerry ' from dual;

Method Three: Split the original string

sql> Select ' 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. Method also has three

• Method One: Use escape characters

SQL > Select ' 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 true with escape characters, except in a different way.

SQL > Select ' Test ' 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 ' the ASCII code

SQL > Select ' It ' | | Chr (39) | | ' Fine ' from dual;

Problem solving:

Select Rs.fname,rs.fcell,rs.fkorgid,rs.flongnumber,rs.fdisplayname,ts.fid as Houseid,Ts.fid | | ' _ ' | | Ts.fkroomid QMF, Ts.fgardenname,ts.froomnumber,ts.fbuildingname,ts.fkgardenid,ts.froompattern
From
(select RowNum rn, Person.*,org.fid as fkorgid,org.fname as Orgname,org.flongnumber,org.fdisplayname from
T_hr_person person
INNER JOIN t_hr_personposition personposition on Person.fid=personposition.fkpersonid
INNER JOIN t_hr_position position on Personposition.fkpositionid=position.fid
INNER JOIN t_hr_org org on position.fkorgid = Org.fid
Left join T_hr_positiontype PositionType on Position.fkpositiontypeid=positiontype.fid
where Personposition.fisprimary=1
and positiontype.fbiztype= ' salesman '
and person.fkcontrounitid= ' 199aad42-ad8d-4eec-8281-657bcc6c9f22 '
and org.ftype= ' intermediary '
and person.fpoststatus! = ' Left '
) RS, (select RowNum rn, t.* from T_broker_house t where t.fisnew= ' 1 ') TS
where Rs.rn = Ts.rn
and ts.fgardenname not like ' owner resource import% '
And Froomnumber is not NULL
And fgardenname is not NULL
And Ts.froompattern is not NULL
and fgardenname not like '% haha disk% '
and ts.fgardenname not like '%,% '

How to insert special characters:& and ' (Multiple solutions) in Oracle