What is the escape character in oracle-Oracle-php.cn

What is the escape character in oracle

WBOY

Release： 2022-01-25 14:12:48

Original

7646 people have browsed it

Escape characters in Oracle: 1. Single quotes, used to quote strings or escape themselves; 2. Double quotes, used to wrap illegal format characters; 3. "&" That is, the and symbol is used to connect two escapes themselves; 4. Escape is used to specify a non-special symbol as an escape character.

What is the escape character in oracle

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What are Oracle's escape characters

1. Oracle special character escape

Keywords: oracle escape

#Execute in plsql:

update userinfo set pageurl=&#39;myjsp?page=1&pagesize=10&#39; where id=&#39;test&#39;

Copy after login

This sql statement puts a url address into the pageurl field of the database, but it is not so ideal when executed because there is an Oracle special character in it. , needs to be escaped, that is the character '&'.

How to deal with the special characters in the above example?

Two methods:

1) update userinfo setpageurl='myjsp?page=1'||'&'||'pagesize=10' where id='test'

2) update userinfo setpageurl='myjsp?page=1'||chr(38)||'pagesize=10' where id='test'

where || is a hyphen, chr( 38) It is consistent with ASCII character transcoding.

In plsql, you can also set define off to turn off special characters, and you can also use show define to view some specially defined characters.

2. How to escape special characters in oracle

Question: How to escape underscore_

select * from ng_values where name like &#39;lady_%&#39;

Copy after login

jieguo The result shows lady_test,lady_test,lady1

is correct The result should be: lady_test, lady_test

excluding lady1

Please provide the escape method, 3ks

answer:

select ... from ... where ... like &#39;/_%&#39; escape &#39;/&#39;;

Copy after login

insert into t(col) values(chr(ascii(&#39;&&#39;))) ；

Copy after login

(Method 1)

Example: Insert special characters '&'

SQL> SHOW DEFINE
define "&" (hex 26) ? <--- DEFINE的default值是 ‘&’
SQL> SET DEFINE OFF
SQL> SHOW DEFINE
define OFF
SQL> INSERT INTO <table_name> VALUES (&#39;AT&T&#39;);

Copy after login

1 row created

(Method 2 )

SQL> SHOW ESCAPE
escape OFF <--- ESCAPE的default值是 OFF
SQL> SET ESCAPE ON
SQL> SHOW ESCAPE
escape "\" (hex 5c)
SQL> INSERT INTO temp_table VALUES (&#39;select * from emp where ename =\&1&#39;);

Copy after login

1 row created.

；

Several test methods:

SELECT &#39;myjsp?page=1&pagesize=10&#39; FROM dual;
SELECT &#39;myjsp?page=1&pagesize=10&#39; FROM dual;
SQL>   create   table   a  (b   varchar2(10));  
Table   created  
SQL>   insert   into   a  values(&#39;_a&#39;);

Copy after login

1 row inserted

SQL>   select   B   from  A   where   instr(b,&#39;_a&#39;)>0;  
B  
----------  
_a  
select   B   from   A  where   b   like   &#39;%\_a%&#39;  escape   &#39;\&#39;

Copy after login

Start the transfer first Symbol

set escape on

select   B   from   A  where   b   like   &#39;%\_a%&#39;  escape   &#39;\&#39;;
select   B   from   A  where   b   like   &#39;%\_a%&#39;  escape   &#39;\&#39;;

Copy after login

Note that

double quotation mark" is not an escape character in Oracle, so it can be stored directly without escaping.

oracle escape characters are:

1, single quotes

Single quotes have two functions in ORACLE: 1) Quote a string; 2) Escape itself.

When quoting a string, single quotes appear in pairs; and escaping itself must also be an even number, so a sql In the statement, single quotes must be in pairs.

Select &#39;abc&#39; a,&#39;&#39; b, &#39;&#39;&#39;&#39; c, &#39; &#39;&#39; &#39; d From Dual;

Copy after login

Complex escaped strings can be split by the connection symbol || for easy understanding. The single quotes around the connection symbol '||' have no relationship unless '||' is as part of a string (this is very common in dynamic SQL).

Select &#39;name&#39; || &#39;&#39;&#39;&#39;&#39;&#39; a, &#39;name&#39;&#39;&#39;&#39;&#39; b From Dual;
Output:name&#39;&#39;　　name&#39;&#39;

Copy after login

2, double quotes

1) Generally speaking, characters The double quotes in the string are only treated as an ordinary character.

At this time, the double quotes do not need to appear in pairs:

Select &#39;hh24"小时""mi""分"""ss"秒"&#39; Results From Dual;
Output:hh24"小时""mi""分"""ss"秒"

Copy after login

2) When appearing in the format string of to_char When, double quotes have a special function, which is to wrap illegal format characters to avoid ORA-01821: date format not recognized errors.

That is to say, after removing the double quotes and the characters they contain, The remainder should be a legal format string.

to_char will ignore double quotes when processing the format string:

Select To_Char(Sysdate, 'hh24"hour"mi"minute" ss"seconds"') As "Current Time/Hour Minutes Seconds" From Dual;

Output:09 hours 05 minutes 08 seconds--this application also exists in the alias

The format string is 'hh24" Hour "mi" minute "ss" second"';

After removing the double quotes, what remains is 'hh24miss', which is a legal format string.

But even if you really don’t know the usage of double quotes, you can still handle it like this, although it is a little more troublesome:

Select To_Char(Sysdate, 'hh24') || 'Hour' || To_Char (Sysdate, 'mi') || 'Min' || To_Char(Sysdate, 'ss') || 'Second' As Result From Dual;

Output:09 hours, 05 minutes and 08 seconds

3,& (and symbol)

is used to identify/set custom variables, followed by the variable name; if it needs to be used as a character, you need to use Chr (38), or Connect two && (escape itself)

Select Chr(38) a, &#39;Tom&&Jerry&#39; b, &#39;Gun &#39;&#39;N Roses&#39; c From Dual;
Output:&    Tom&Jerry    Gun &#39;N Roses

Copy after login

4, Escape(specify escape character)

Specify a non-special symbol as the escape character, mostly used for wildcard escape

Select Table_Name From User_Tables Where Table_Name Like &#39;TB=_%=_%&#39; Escape &#39;=&#39;;
Output:TB_STUDENT_SCORE--筛选以TB开头,中间有两个_的表名

Copy after login