The insert and insert escape single quotation marks in Oracle

Source: Internet
Author: User
I. Insert the single quotation marks (single quotation marks) and the & Symbol in Oracle

When importing a batch of data to Oracle today, I encountered a problem: toad prompts me to assign a value to a custom variable amp. At first I was wondering that data is a series of insert statements, how can I have custom variables? Then I searched for the keyword "amp" and found that it was because the content of a field in the inserted data was as follows:

Http://mobile.three.com.hk/3DX? Uid = 1, 0676
& Amp;

SID = rt_060908

Oracle regards the URL parameter connector & amp; as a custom variable, so I need to assign a value to the variable amp. What should we do? There are three methods:

· Method 1: Add set define off before the SQL statement to be inserted and execute it in batches 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 setting is 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!

How can I insert a single quotation mark in the field? For example, it's fine. There are also three methods

· Method 1: Use escape characters

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

Note: What are the meanings of the four single quotes? First, the first and last are character strings in Oracle, and there is no objection. So what does the second and third sign mean? The second 'is an escape character.
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.

· Method 3: replace 'with CHR (39) in SQL, because CHR (39) is the' ASCII code

SQL> select 'it' | CHR (39) | 'fine 'from dual;

 

Ii. Oracle select queries General escape characters in the where Condition

Usage:
Select distinct T. table_name from user_tab_columns t
Where T. table_name like 'A/_ % 'escape '/'
Characters After escape can be defined at will. This statement is used to find all table names starting with. Because _ represents a character in SQL like, escape characters must be used.
When multiple like characters exist, you must specify the transfer character after each like character. For example:
Select distinct T. table_name from user_tab_columns t
Where T. table_name like 'A/_ % 'escape '/'
Or T. table_name like 'B/_ %' escape '/'
Or T. table_name like 'C/_ % 'escape '/'
Or T. table_name like 'd/_ % 'escape '/'
This statement can be used to find the table names starting with "A _" "B _" "C _" and "D.
If you write
Select distinct T. table_name from user_tab_columns t
Where T. table_name like 'A/_ %'

 

Or T. table_name like 'B/_ %'
Or T. table_name like 'C/_ %'
Or T. table_name like 'd/_ % 'escape '/'
Then we can find "A/" with at least one character "B/" after it and at least one character "C/" after it and at least one character "D _" after it _" table Name

 

 

From: http://hi.baidu.com/lxl_buat/blog/item/39963ff7fa5bb028720eecad.html

 

WhereSet define off;
It seems that the execution is not successful in PL/SQL!

Related Article

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.