OCP 1Z0 051 83

83. 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 ; 

补齐长度应补至名称总长度，c 与 d只计算子串长度，不对。

SQL> CREATE OR REPLACE VIEW customers(CUST_NAME)
  2  AS
  3  SELECT 'Lex De Haan' from dual union all
  4  SELECT 'Renske Ladwig' from dual union all
  5  SELECT 'Jose Manuel Urman' from dual union all
  6  SELECT 'Jason Mallin' from dual;
View created

SQL> SELECT cust_name,
  2         instr(cust_name, ' ', 1, 2) AS instr1,
  3         instr(cust_name, ' ', -1, 2) AS instr2,
  4         length(cust_name) AS l1,
  5         length(cust_name) - instr(cust_name, ' ') AS l2
  6    FROM customers;
CUST_NAME             INSTR1     INSTR2         L1         L2
----------------- ---------- ---------- ---------- ----------
Lex De Haan                7          4         11          7
Renske Ladwig              0          0         13          6
Jose Manuel Urman         12          5         17         12
Jason Mallin               0          0         12          6
4 rows selected

SQL> SELECT lpad(substr(cust_name, instr(cust_name, ' ')),
  2              length(cust_name),
  3              '*') "CUST NAME"
  4    FROM customers
  5   WHERE instr(cust_name, ' ', 1, 2) <> 0;
CUST NAME
--------------------------------------------------------------------------------
*** De Haan
**** Manuel Urman
2 rows selected
Executed in 0.015 seconds
SQL> SELECT lpad(substr(cust_name, instr(cust_name, ' ')),
  2              length(cust_name),
  3              '*') "CUST NAME"
  4    FROM customers
  5   WHERE instr(cust_name, ' ', -1, 2) <> 0;
CUST NAME
--------------------------------------------------------------------------------
*** De Haan
**** Manuel Urman
2 rows selected
Executed in 0.031 seconds
SQL> SELECT lpad(substr(cust_name, instr(cust_name, '  ')),
  2              length(cust_name) - instr(cust_name, ' '),
  3              '*') "CUST NAME"
  4    FROM customers
  5   WHERE instr(cust_name, ' ', -1, -2) <> 0;
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
ORA-01428: 参数 '-2' 超出范围
SQL> SELECT lpad(substr(cust_name, instr(cust_name, '  ')),
  2              length(cust_name) - instr(cust_name, ' '),
  3              '*') "CUST NAME"
  4    FROM customers
  5   WHERE instr(cust_name, ' ', 1, 2) <> 0;
CUST NAME
--------------------------------------------------------------------------------
Lex De
Jose Manuel
2 rows selected

Answer: AB