【OCP】Oracle 11g OCP 1Z0-051 003

3. You need to extract details of those products in the SALES table where the PROD_ID column
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/，如需轉載，請註明出處，否則將追究法律責任。