What are the oracle escape characters?

oracle escape characters include: 1. Single quotation mark, which can quote a string or escape itself; 2. Double quotation mark, which wraps illegal format characters; 3. and symbol, which connects two escapes Self; 4. Escape, specify a non-special symbol as the escape character.

oracle escape characters are:

1, single quote

In ORACLE, single quotes have two functions: 1) quoting strings; 2) escaping themselves.

When quoting a string, single quotes appear in pairs; and the escape of itself must be an even number, so in a SQL statement, single quotes must be in pairs.

Select 'abc' a,'' b, '''' c, ' '' ' d From Dual;

Complex escaped strings can be split using the connector || for easy understanding. The single quotes around the connection symbol '||' have no relation whatsoever, unless the '||' is part of a string (which is very common in dynamic SQL).

Select 'name' || '''''' a, 'name''''' b From Dual;
Output:name''　　name''

2, double quotes

1) Generally speaking, double quotes in strings are only treated as ordinary characters.

At this time, 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, double quotes have a special effect, It is to wrap illegal format characters to avoid ORA-01821: date format not recognized error.

In other words, after removing the double quotes and the characters contained therein, what remains should be a legal format string.

to_charDouble quotation marks will be ignored when processing the format string:

Select To_Char(Sysdate, 'hh24"小时"mi"分"ss"秒"') As "当前时间/时分秒"  From Dual;
Output:09小时05分08秒 --别名中也有此应用

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') || '小时' || To_Char(Sysdate, 'mi') || '分' || To_Char(Sysdate, 'ss') || '秒' As Result From Dual;
Output:09小时05分08秒

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 two consecutive &&(replace (define 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开头,中间有两个_的表名

Related learning recommendations: oracle database learning tutorial

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