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 ;
How is option B correct?
In case of B: INSTR starts from the end of cust_name and checking if exists second space. Position has value ‘-1’ which means – start searching from the end.
plz explain me this clause WHERE INSTR(cust_name, ‘ ‘,-1,2)0;
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’) “CUST NAME” FROM customers
WHERE INSTR(cust_name, ‘ ‘,1,2)0;
cust_name= ‘Jose Manuel Urman’
o/p–
=LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),LENGTH(cust_name),’*’)
=LPAD(SUBSTR(cust_name,5),LENGTH(cust_name),’*’)
=LPAD(‘ Manuel Urman’,LENGTH(cust_name),’*’)
=**** Manuel Urman
INSTR eg :
SHOW INSTR(‘Corporate Floor’,’or’, 3, 2)
14
GENERAL :
INSTR (‘MAIN STRING’,’String_to_search’,start_point,nth occurrence)
Explanation for WHERE INSTR(cust_name, ‘ ‘,-1,2)0;
Consider the below example
Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR. Oracle then searches backward for the second occurrence of OR, and finds that this second occurrence begins with the second character in the search string :
SELECT INSTR(‘CORPORATE FLOOR’,’OR’, -3, 2) “Reversed Instring”
FROM DUAL;
Reversed Instring
—————–
2
Ref: Oracle Documentation
plzz admin explain instr(cust_name,’ ‘) this function
What is the difference between A and B?
instr(cust_name,’ ‘) retruns the position number of first ‘ ‘ in the name. Example: if the name is ‘Lex Dan Haan’ then instr(cust_name,’ ‘) will return 4.
A. and B. is correct
SQL> edit
Wrote file afiedt.buf
1 select
2 INSTR(‘Jose Manuel Urman’,’ ‘)
3 INSTR(‘Jose Manuel Urman’,’ ‘,1,2) test2,
4 LENGTH(‘Jose Manuel Urman’) test3,
5 SUBSTR(‘Jose Manuel Urman’,INSTR(‘Jose Manuel Urman’,’ ‘),LENGTH(‘Jose Manuel Urman’)) test4,
6 LPAD(SUBSTR(‘Jose Manuel Urman’,INSTR(‘Jose Manuel Urman’,’ ‘)),LENGTH(‘Jose Manuel Urman’),’*’) test5
7 from dual
8* where INSTR(‘Jose Manuel Urman’,’ ‘,1,2)0 — here this where clause is not needed(because we are querying the dual –table with just one single value) but I’ve wanted to present it as in the exam.
SQL>
SQL> /
TEST1 TEST2 TEST3 TEST4 TEST5
———- ———- ———- ————- —————–
5 12 17 Manuel Urman **** Manuel Urman