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!