What is the escape character in oracle

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.

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='myjsp?page=1&pagesize=10' where id='test'

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 'lady_%'

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 '/_%' escape '/';

3,

insert into t(col) values(chr(ascii('&'))) ；

(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;);

/

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;);

1 row created.

；

Several test methods:

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

1 row inserted

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

Start the transfer first Symbol

set escape on

select   B   from   A  where   b   like   '%\_a%'  escape   '\';
select   B   from   A  where   b   like   '%\_a%'  escape   '\';

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 'abc' a,'' b, '''' c, ' '' ' d From Dual;

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 'name' || '''''' a, 'name''''' b From Dual;
Output:name''　　name''

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 'hh24"小时""mi""分"""ss"秒"' Results From Dual;
Output:hh24"小时""mi""分"""ss"秒"

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, 'Tom&&Jerry' b, 'Gun ''N Roses' c From Dual;
Output:&    Tom&Jerry    Gun 'N Roses

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 'TB=_%=_%' Escape '=';
Output:TB_STUDENT_SCORE--筛选以TB开头,中间有两个_的表名

Recommended tutorial: "

Oracle Video Tutorial

"

The above is the detailed content of What is the escape character in oracle. For more information, please follow other related articles on the PHP Chinese website!