OCP-1Z0-051 第83题 INSTR,SUBSTR,LPAD函数综合使用

最新推荐文章于 2024-06-29 17:59:58 发布

hello_holly

最新推荐文章于 2024-06-29 17:59:58 发布

阅读量5.3k

点赞数 2

分类专栏： OCP-1Z0-051 文章标签： OCP-1Z0-051 INITCAP INSTR SUBSTR 11g

本文链接：https://blog.csdn.net/holly2008/article/details/25186117

版权

OCP-1Z0-051 专栏收录该内容

220 篇文章 6 订阅

订阅专栏

一、原题

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin

You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
*** De Haan
**** Manuel Urman

Which two queries give the required output? (Choose two.)
A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
       FROM customers
    WHERE INSTR(cust_name, ' ',1,2)<>0;

B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
       FROM customers
    WHERE INSTR(cust_name, ' ',-1,2)<>0;

C.SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-
                   INSTR(cust_name,' '),'*') "CUST NAME"
       FROM customers
    WHERE INSTR(cust_name, ' ',-1,-2)<>0;

D.SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR(cust_name,' '),'*') "CUST NAME"
       FROM customers
    WHERE INSTR(cust_name, ' ',1,2)<>0;

答案：AB

二、题目翻译

下面是CUSTOMERS表CUST_NAME列的数据:
CUST_NAME
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
现在要提取那些有三个名字的客户，第一个名字用*号替换，比如下面这两个:
CUST NAME
*** De Haan
**** Manuel Urman
哪两个查询能得出想要的结果(选择2个)?

三、题目解析

A选项和B选项正确，
      先用INSTR(cust_name,' ')找出第一个空格的位置，
      然后，SUBSTR(cust_name,INSTR(cust_name,' '))从第一个空格开始往后截取字符串到末尾，结果是第一个空格以后所有的字符,
      最后，LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原来的长度，不足的部分用*填充，也就是将第一个空格前的位置，用*填充。
      A选项和B选项的区别是where后过滤是否有三个名字的方法不同，
      A选项INSTR(cust_name, ' ',1,2)从第一个位置，从左往右，查找第二次出现的空格，如果返回非0值，则说明有第二个空格，则有第三个名字，
      B选项INSTR(cust_name, ' ',-1,2)从最后一个位置，从右往左，查找第二次出现的空格，如果返回非0值，则说明有第二个空格，则有第三个名字。

C选项不正确，INSTR(cust_name, ' ',-1,-2)第四个参数，是第几次出现，这里不能为负。
D选项不正确，填充长度LENGTH(cust_name)- INSTR(cust_name,' ')，是总长度，减去第一个空格的位置，也就是说，总长度是第一个空格后面的字符串的长度，那就不用*填充了，不满足题目要求。

四、测试
下面是ABCD四个选项的测试结果：

SQL> with customers as
2   ( select 'Lex De Haan' CUST_NAME from dual
3      union all
4      select 'Renske Ladwig' CUST_NAME from dual
5      union all
6      select 'Jose Manuel Urman' CUST_NAME from dual
7      union all
8      select 'Jason Mallin' CUST_NAME from dual
9    )
10 SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
11 FROM customers
12 WHERE INSTR(cust_name, ' ',1,2)<>0;

CUST NAME
--------------------------------------------------------------------------------------------
*** De Haan
**** Manuel Urman

SQL> with customers as
2   ( select 'Lex De Haan' CUST_NAME from dual
3      union all
4      select 'Renske Ladwig' CUST_NAME from dual
5      union all
6      select 'Jose Manuel Urman' CUST_NAME from dual
7      union all
8      select 'Jason Mallin' CUST_NAME from dual
9    )
10 SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
11 FROM customers
12 WHERE INSTR(cust_name, ' ',-1,2)<>0;

CUST NAME
---------------------------------------------------------------------------------------------
*** De Haan
**** Manuel Urman

SQL> with customers as
2   ( select 'Lex De Haan' CUST_NAME from dual
3      union all
4      select 'Renske Ladwig' CUST_NAME from dual
5      union all
6      select 'Jose Manuel Urman' CUST_NAME from dual
7      union all
8      select 'Jason Mallin' CUST_NAME from dual
9    )
10 SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-
11 INSTR(cust_name,' '),'*') "CUST NAME"
12 FROM customers
13 WHERE INSTR(cust_name, ' ',-1,-2)<>0;
FROM customers
     *
ERROR at line 12:
ORA-01428: argument '-2' is out of range

SQL> with customers as
2   ( select 'Lex De Haan' CUST_NAME from dual
3      union all
4      select 'Renske Ladwig' CUST_NAME from dual
5      union all
6      select 'Jose Manuel Urman' CUST_NAME from dual
7      union all
8      select 'Jason Mallin' CUST_NAME from dual
9    )
10 SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR(cust_name,' '),'*') "CUST NAME"
11 FROM customers
12 WHERE INSTR(cust_name, ' ',1,2)<>0;

CUST NAME
---------------------------------------------------------------------------------------------------------------
De Haa
Manuel Urma