【OCP】Oracle 11g OCP 1Z0-051 003
contains the string '_D123'.
Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
答案: B
考點:模糊查詢中查詢的字串本身包含_、%萬用字元時,需要使用escape進行轉義
關鍵字:ESCAPE '\'
【解析實驗】
where條件中使用like進行模糊查詢匹配時,可以使用萬用字元,
_ 可以匹配任意單個字元
% 可以匹配任意零個或多個字元
如果要匹配的字串當中本身就包含這樣的符號,需要使用escape進行轉義,取消這兩個符號的特殊含義,作為普通字元
建立測試表,構造測試資料
SCOTT@PROD>create table test as select * from emp where deptno=20;
SCOTT@PROD>update test set ename='xinghao_lv' where empno=7788;
SCOTT@PROD>update test set ename='xinghaolv' where empno=7902;
SCOTT@PROD>commit;
SCOTT@PROD>select * from test;
查詢test表中姓名中包含 MI 的員工資訊
SCOTT@PROD>select * from test where ename like '%MI%';
查詢test表中姓名中包含 _ 的員工資訊 (錯誤結果)
SCOTT@PROD>select * from test where ename like '%_%';
上述語句會把test表中所有資訊顯示出來,因為%匹配任意多個字元,_匹配任意單個字元,所以 like '%_%' 實際匹配的是所有。
需要使用escape轉義,將 _ 作為萬用字元的功能取消。
SCOTT@PROD>select * from test where ename like '%\_%' escape'\';
通常使用'\'作為轉義符,也可以使用其他符號
SCOTT@PROD>select * from test where ename like '%#_%' escape'#';
【官方文件】
SQL Language Reference - 7 Conditions - like condition
The pattern can contain special pattern-matching characters:
An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null.
You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29475508/viewspace-2065268/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【OCP】Oracle 11g OCP 1Z0-051 004Oracle
- 【OCP】Oracle 11g OCP 1Z0-051 005Oracle
- 【OCP】Oracle 11g OCP 1Z0-051 006Oracle
- 【OCP】Oracle 11g OCP 1Z0-051 007Oracle
- 【OCP】Oracle 11g OCP 1Z0-051 001Oracle
- 【OCP】Oracle 11g OCP 1Z0-051 002Oracle
- Oracle 11G OCP 1Z0-051 所有題目解析Oracle
- Oracle?認證專家 (OCP) 11GOracle
- 11g oracle OCP 047題庫解析Oracle
- 11G oracle OCP 047題庫解析Oracle
- 【Oracle】-【OCP】-“滾燙”的OCP經歷Oracle
- Oracle OCP(56)Oracle
- Oracle 11G OCP 1Z0-052 14Oracle
- Oracle 11G OCP 1Z0-052 88Oracle
- Oracle 11G OCP 1Z0-052 89Oracle
- Oracle 11G OCP 1Z0-052 90Oracle
- Oracle 11G OCP 1Z0-052 92Oracle
- Oracle 11G OCP 1Z0-052 93Oracle
- Oracle 11G OCP 1Z0-052 94Oracle
- Oracle 11G OCP 1Z0-052 95Oracle
- Oracle 11G OCP 1Z0-052 96Oracle
- Oracle 11G OCP 1Z0-052 97Oracle
- Oracle 11G OCP 1Z0-052 98Oracle
- Oracle 11G OCP 1Z0-052 99Oracle
- Oracle 11G OCP 1Z0-052 71Oracle
- Oracle 11G OCP 1Z0-052 73Oracle
- Oracle 11G OCP 1Z0-052 74Oracle
- Oracle 11G OCP 1Z0-052 75Oracle
- Oracle 11G OCP 1Z0-052 76Oracle
- Oracle 11G OCP 1Z0-052 77Oracle
- Oracle 11G OCP 1Z0-052 78Oracle
- Oracle 11G OCP 1Z0-052 79Oracle
- Oracle 11G OCP 1Z0-052 80Oracle
- Oracle 11G OCP 1Z0-052 81Oracle
- Oracle 11G OCP 1Z0-052 82Oracle
- Oracle 11G OCP 1Z0-052 83Oracle
- Oracle 11G OCP 1Z0-052 84Oracle
- Oracle 11G OCP 1Z0-052 85Oracle