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博客

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

最新文章