You need to display the first names of all customers from the CUSTOMERS table that contain the
character ‘e’ and have the character ‘a’ in the second last position.
Which query would give the required output?
A.
SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0 AND
SUBSTR(cust_first_name, -2, 1)=’a’;
B.
SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>” AND
SUBSTR(cust_first_name, -2, 1)=’a’;
C.
SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)IS NOT NULL AND
SUBSTR(cust_first_name, 1,-2)=’a’;
D.
SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)=’a’;
Explanation:
The SUBSTR(string, start position, number of characters) function accepts three parameters and
returns a string consisting of the number of characters extracted from the source string, beginning
at the specified start position:
substr(‘http://www.domain.com’,12,6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first
character.
When position is negative, then the function counts backward from the end of string.
substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined by the
input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode
complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position],[nth occurrence of search item]) function
returns a number that represents the position in the source string, beginning from the given start
position, where the nth occurrence of the search item begins:
instr(‘http://www.domain.com’,’.’,1,2) = 18