Oracle special character escape: & amp; and & #39;, oracle special character escape

Source: Internet
Author: User

Oracle special character escape: & and ', oracle special character escape
When we run the SQL show all command in SQL * PLUS, we can find a parameter: define & (hex 26), as shown below:
Concat. (hex 2e)
Copycommit 0
Copytypecheck ON
Define & (hex 26)
Describe DEPTH 1 linenum off indent OFFecho OFF


1. "&" escape this is the setting used in Oracle to identify custom variables. Now we disable it in SQL * PLUS:
SQL Set define OFF;
Run the import script again. OK! Solve the problem.
NOTE: If it is executed in TOAD, we recommend that you disable define in the first line of each script to be imported, otherwise, an error occurs when you import the second script containing special characters.
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.
· Method 2: replace '&' with chr (38) in an SQL statement because chr (38) is the '&' ASCII code.
SQL Select 'Tom '| chr (38) | 'Jerry' from dual;
· Method 3: Split the original string
SQL Select 'Tom '|' & '| 'Jerry' from dual;
We can see that method 1 is the easiest and most efficient. Method 2: because there is a function call process, the performance is slightly poor. Method 3 requires two connection strings, with the worst efficiency!
2. "'" escape-Method 1: Use escape characters
SQL Select 'test' | ''' from dual;
The third is our real content.
· Method 2: escape characters are used in different ways.
SQL Select 'test''' from dual;
Note: The second and third are the escape characters and real content mentioned in method 1 above.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.